Oracle Package Level Constants for Postgres Conversion
I am considering converting an application from Oracle to Postrges which has all the business logic in the database. There is currently a very large package containing about 200 constant variables. Postgres does not support package level variables, so I am discussing how to convert it. I see two possibilities, but I need some opinions on which is better (they both seem very annoying):
- Convert each variable to a function that returns a static value. This seems most likely, but it seems very ugly.
- Extract table from values. The problem is that they are mainly used by other packages / functions. There is also a combination of types (numeric and varshars).
Any ideas?
source to share
I would mix both. The parameters will be saved in the table:
create table package_options ( option_name text, option_value text )
for the convenience of adding new parameters or modifying and returning a function for ease of use in queries:
create function get_option(text) returns text as
$$ select option_value from package_options where option_name=$1 $$
language sql stable;
Also maybe get_int_option(text)
converting the value to int, etc.
You can also add a column option_type
and some constraints that will validate the type.
source to share
I am taking a performance and interface approach since your problem presented it.
Depending on the database you are using, using option 2 may cause a hot spot to appear in your database. Here's a more extreme scenario. Say you have 5000 users logged in, which in turn makes your code run, which in turn selects your package_options table. At any time, you can impress thousands of users with this table.
This might be a problem that PG might not handle concurrency in a way that testing alone will not prove anything. You would have to check this to be sure, but there is something to consider when considering this approach. I would also check your option 1 scenario and see which one works better, not a simple interface to manage and use. Given that the tests required to do this would be relatively straightforward, why not test it out so you don't end up down the road with a poor choice for your use case.
source to share