471,075 Members | 799 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Variable in PostgreSQL 7.4.x

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.

thanx, hlavki

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
2 1611
On Tue, May 04, 2004 at 16:39:55 +0200,
Michal Hlavac <hl****@medium13.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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2
On Tue, May 04, 2004 at 16:39:55 +0200,
Michal Hlavac <hl****@medium13.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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Otis Green | last post: by
reply views Thread by Bill J. | last post: by
1 post views Thread by phil campaigne | last post: by
13 posts views Thread by Nagib Abi Fadel | last post: by
reply views Thread by Shujun Huang | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.