Package Level Constants in Oracle to Postgres Conversion

Question

I am looking at converting an application from Oracle to Postrges that has all the business logic in the database. Currently there is a very large package that has about 200 public constant variables. Postgres does not support package level variables so I am debating how to convert it. I see two possibilities but need some opinions about which is better (they both seem very nasty):

1. Convert each variable to a function that returns a static value. This seems the most likely but it seems very ugly.
2. Make a table out of the values. The issue with this is that they are used mainly by other packages/functions. Also there is a mix of types (numeric vs varchars).

Any ideas?

Answer 1

I'd go with option 1, should be reasonably easy to write a script to automatically do this for you and you then keep the package as close as possible to its original definition.

Answer 2

I'd mix both options. Options would be saved in a table:

create table package_options ( option_name text, option_value text )

for ease of adding new options or modification and returned by 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;

There may also be get_int_option(text), converting value to int etc.

You can also add option_type column and some constraints, that will check type validity.

Answer 3

I'm taking a performance approach vs a interface approach as your problem presented it.

Depending on your database usage going with option 2 could cause a hot spot in your database. Here's a more extreme scenario - Say that you have 5000 users logged in nailing the system which in turn causes your code to fire off which in turn selects against your package_options table. At any given moment you may have thousands of users hitting that table.

This may be an issue it may not PG handles concurrency well so only testing will prove anything. You would have to test it to see for sure, but is something to keep in mind when considering that approach. I would also test your option 1 scenario as well and see which performs better in addition to being a simple interface to manage and use. Considering the tests required for this would be relatively simple why not test it so you don't get stuck down the road with a poor choice for your usage scenario.