PostgreSQL: Is it possible to define a session variable with language and use it in views?
Here's a simplified schematic example:
Table l10n ( l10n_id SMALLINT, code VARCHAR(5) )
Table product ( product_id INT, ..language-neutral columns.. )
Table product_l10n ( product_id INT, l10n_id SMALLINT, ..language-specific columns.. )
The query for products with localized data is done as follows:
SELECT *
FROM product a
LEFT JOIN product_l10n b ON b.id = a.id
LEFT JOIN l10n c ON c.id = b.id
WHERE c.code = 'en-US';
To avoid this big request, I would like to use views.
The basic idea is to create a view based on the above query, without a where clause.
Then the request for products will be as follows:
SELECT * FROM product_view WHERE c.code = 'en-US';
Another idea is to have a variable containing the language tag defined for each DB connection / session.
The view will be based on the first query using the variable in the where clause.
A variable set in the current DB session will query queries for products:
SELECT * FROM product_view;
So my question is: can it do this? How?
This is possible using custom variables in postgresql.conf. See the document User Parameters .
In postgresql.conf:
custom_variable_classes = 'myproject'
myproject.l10n_id = 'en-US'
At the beginning of a database session (by default, the parameter is set at the session level):
SET myproject.l10n_id = 'en-US';
In views:
WHERE c.code = current_setting('myproject.l10n_id')
But ... I don't like to define a variable for the whole server. Is there a way to achieve the same but based on the database?
Thanks in advance,
Pascal
PS: I have another question regarding using l10n_id as SMALLINT or directly as ISO code in VARCHAR (5). See http://stackoverflow.com/questions/1307087/how-to-store-language-tag-ids-in-databases-as-smallint-or-varchar (sorry, only 1 url for new users :-)
source to share
Ok, what exactly is the problem with creating a variable for the whole server? It doesn't affect any other connection / request, so should be fine.
Another approach can be taken by the fact that every connection can be found using the backend pid, which can be obtained with
select pg_backend_pid();
So, you can create a table with columns like:
- backend_pid int4
- variable_name text
- text_value
with a primary key (backend_pid, variable_name) and provide a set of functions that get a value and set a value by internally checking pg_backend_pid.
There is still the problem of what happens if the connection is closed without "clearing" (removing all variables) and a new connection starts - thus getting variables from the previous connection, but this is usually not very likely.
I thought about it a bit and wrote a blog post with exact sql that will create the table and functions it needs to work.