CODEWITHSUNDEEP
postgresql
Chris
Chris

Reputation: 21

Database Wide Constant

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?

Upvotes: 1

Views: 90

Answers (2)

Abelisto
Abelisto

Reputation: 15614

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.

Upvotes: 2

dmg
dmg

Reputation: 4481

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.

Upvotes: 1

Related Questions