Database Wide Constant

Question

I have several type columns in my database schema.

For example there is a type_id column which can either be the primary, secondary, work or old address.

Querying for all old addresses of an employee would look like this:

select a.*
from employees e
join employeeaddresses ea on ea.employee_id = e.employee_id
join addresses a on ea.adress_id = a.address_id
where ea.type_id = 4 -- old addresses

I would like to introduce a database wide constant TYPE_OLDADDRESS that is internally mapped to 4.

So the query would look like this:

select a.*
from employees e
join employeeaddresses ea on ea.employee_id = e.employee_id
join addresses a on ea.adress_id = a.address_id
where ea.type_id = TYPE_OLDADDRESS;

Is postgres capable of this or is there a workaround for this?

I would use a function: `where ea.type_id = type_oldaddress()` — , Oct 31 '16 at 22:28
Some possible duplicates: http://stackoverflow.com/questions/13316773/is-there-a-way-to-define-a-named-constant-in-a-postgresql-query and http://stackoverflow.com/questions/14261035/user-defined-variables-in-postgresql — Erwin Brandstetter, Nov 01 '16 at 00:21

score 2 · Answer 1 · answered Oct 31 '16 at 18:52

You could to set/create the session/role/database wide parameters like:

set parameter.name to value; -- for the current session only
alter role rolename set parameter.name to value; -- for the specified role, relogin required
alter database dbname set parameter.name to value; -- for the specified database, relogin required

Then get it using current_setting function:

select current_setting('parameter.name');

The function returns the text value so the explicit type cast could be necessary.

To remove the parameter definition from the role/database use reset instead of set key word:

alter database dbname reset parameter.name;

The dot in the custom parameter name is required.

score 1 · Answer 2 · answered Oct 31 '16 at 18:18

Create a table.

For example:

CREATE TABLE global_constants(
   name char(20),
   value int);

insert the corresponding value

insert into global_constants values('TYPE_OLDADDRESS',4);

then you can use a subquery:

select a.*
from employees e
join employeeaddresses ea on ea.employee_id = e.employee_id
join addresses a on ea.adress_id = a.address_id
where ea.type_id = (select value from global contantants where name = 'TYPE_OLDADDRESS');

You could also use a UDF. It all depends on the syntax you prefer.

Database Wide Constant

2 Answers2