PostgreSQL: Can one define a session variable with the language and use it in views?

Question

Here's a simplified example of schema:

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.. )

Querying for products with localized data is done like this:

    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 that big fat query, I would like to use views.
The basic idea is to create a view based on the above query without the where clause.
Querying for products would then become:

    SELECT * FROM product_view WHERE c.code = 'en-US';

Another idea would be to have a variable containing the language tag, defined for each DB connection/session.
The view would be based on the first query using the variable in the where clause.
The variable being set in the current DB session, querying for products would then be as simple as this:

    SELECT * FROM product_view;

So my question is: Can one do that? How?

It's feasible using custom variables in postgresql.conf. See the doc Customized Options.

In postgresql.conf:

    custom_variable_classes = 'myproject'
    myproject.l10n_id = 'en-US'

At the beginning of the DB session (the param is set at session level by default):

    SET myproject.l10n_id = 'en-US';

In views:

    WHERE c.code = current_setting('myproject.l10n_id')

But... I don't like having to define a variable for the whole server. Is there a way to achieve the same but on a per database basis?

Thanks in advance,
Pascal

PS: I've postes another question regarding using l10n_id as SMALLINT or directly as the ISO code in a 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 :-)

Answer 1

Well, what exactly is the problem with making the variable for whole server? It doesn't influence any other connection/query, so it should be fine.

Another approach can be used by the fact that each connection can be found using backend pid, which can be obtained with

select pg_backend_pid();

So, you could create a table, with columns like:

• backend_pid int4
• variable_name text
• variable_value text

with primary key on (backend_pid, variable_name) and provide set of functions that get the value and set the value, internally checking pg_backend_pid.

There is still a problem what happens if connection will close without "cleaning up" (removing all variables), and new connection will start - thus getting variables from previous connection, but this is usually not very likely.

I thought a bit about it, and wrote blog post with exact sql that will create table and functions that are required to make it work.

Answer 2

i am not satisfied with u'r example. can u define in simple language. what is session ?