Re: Variable in PostgreSQL 7.4.x

On Tue, May 04, 2004 at 16:39:55 +0200,
Michal Hlavac <hlavki(at)medium13(dot)sk> wrote:
> Hello,
>
> I am programming web portal. I have table c_language with attributes
> i_language_id INT4 PK
> c_code CHAR(2) NOT NULL UNIQUE
> v_name VARCHAR(32)
>
> and tables referenced to c_language. I need create some variable, which
> contain value of i_language_id during one web request.
>
> at start of request I create temporary table f.e.:
> SELECT i_language_id INTO lang FROM c_language WHERE c_code='EN';
>
> There is stored procedure get_lang(); which only do
> SELECT i_language_id FROM land;
>
> Every view is build like f.e.:
> SELECT * FROM TABLE WHERE i_language_id=get_lang();
>
> this create, that on my application layer I need not write language
> condition into every query.
>
> but creating of temporary table in every web request is IMHO slow. Is
> there another way, how to store temporary variable into postgres.

If the languages you support are a subset of what your postgres installation
supports than you can probably store the language in the lc_messages
without breaking anything. The value can be retrived via a function in
your views. This wouldn't leave behind tuples to be vacuumed. So if it
will work in your situation, I think it is probably the best. (In 7.5
there is some sort of custom GUC feature that you might be able to
use in a similar fashion.)

Otherwise you can created a table that has either backend pids or the
value of a sequence as the key and the appropiate language for this session
as a second column. You can use pg_backend_pid() or currval('your_seq_name')
in views. If you do things this way you are going to need to vacuum this
table very frequently. You may or may not want an index on the table.
If you can keep up with vacuuming, you are probably better off without
an index.