session variable

sad wrote:

Good day

is it possible to define user variables in session ?
If not then is it planned to implement ?
or it is principially impossible in PosqtgreSQL

psql has variables, but in general we don't support session varibles.
You could create a temp table and put a value in there easily.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Could you possible have some type of variable (preferably the Transaction
ID) that can identify an individual process?

If something like this already exist, then disregard this post.

"Bruce Momjian" <pgman@candle.pha.pa.us> wrote in message
news:200309011657.h81Gva817128@candle.pha.pa.us...

sad wrote:

Good day

is it possible to define user variables in session ?
If not then is it planned to implement ?
or it is principially impossible in PosqtgreSQL

psql has variables, but in general we don't support session varibles.
You could create a temp table and put a value in there easily.

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania

19073

On Monday 01 September 2003 20:57, you wrote:

sad wrote:

Good day

is it possible to define user variables in session ?
If not then is it planned to implement ?
or it is principially impossible in PosqtgreSQL

psql has variables, but in general we don't support session varibles.
You could create a temp table and put a value in there easily.

that's ok, unless i need to access it in a trigger.

where may i ask a programming and perfomance question?
i think mailing such questions to "BUGS" is not a good idea.

sad wrote:

is it possible to define user variables in session ?
If not then is it planned to implement ?
or it is principially impossible in PosqtgreSQL

psql has variables, but in general we don't support session varibles.
You could create a temp table and put a value in there easily.

that's ok, unless i need to access it in a trigger.

No problem to use a temp table in a trigger (which is local to the
session), I do so myself. Still, session variables would be nice, making
coding a little bit more comfortable.

where may i ask a programming and perfomance question?
i think mailing such questions to "BUGS" is not a good idea.

Just use the pgsql-sql and pgsql-performance mailing lists.

Regards,
Andreas

On Tuesday 02 September 2003 16:40, you wrote:

No problem to use a temp table in a trigger (which is local to the
session), I do so myself. Still, session variables would be nice, making
coding a little bit more comfortable.

(it would be very good if you implement session variables in PostgreSQL.)

The first problem using temp table is:

CREATE TABLE locals (name text, value text);

CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';

SELECT test_locals_access() ;

column
-------------
<null>

CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');

SELECT test_locals_access() ;

column
-------------
<null> <=== the Function seing global table

SELECT value FROM locals WHERE name='n1';

value
---------
xxx

the second problem may be resolved with your advise.
look:

i want to log operations on the data in some tables.
so i declared sufficient triggers which write to the log-table.

and now i want to mark each log-record with the "operator_id"
(e.g. to log who made an update)

what possible ways are there ?

if i use temp table to inform the triggers about "operator_id"
then i have problems in manual updates of these tables
(temp table in my session needed too)

Bupp Phillips wrote:

Could you possible have some type of variable (preferably the Transaction
ID) that can identify an individual process?

There's pg_backend_pid() for 7.4 and backend_pid() as contrib module for
earlier releases.

Regards,
Andreas

sad writes:

The first problem using temp table is:

CREATE TABLE locals (name text, value text);

CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';

SELECT test_locals_access() ;

column
-------------
<null>

CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');

SELECT test_locals_access() ;

column
-------------
<null> <=== the Function seing global table

This is a problem in plpgsql, not in temporary tables.

the second problem may be resolved with your advise.
look:

i want to log operations on the data in some tables.
so i declared sufficient triggers which write to the log-table.

and now i want to mark each log-record with the "operator_id"
(e.g. to log who made an update)

what possible ways are there ?

if i use temp table to inform the triggers about "operator_id"
then i have problems in manual updates of these tables
(temp table in my session needed too)

Use session_user, or restrict permissions on the temporary table.

--
Peter Eisentraut peter_e@gmx.net

sad wrote:

On Monday 01 September 2003 20:57, you wrote:

sad wrote:

Good day

is it possible to define user variables in session ?
If not then is it planned to implement ?
or it is principially impossible in PosqtgreSQL

psql has variables, but in general we don't support session varibles.
You could create a temp table and put a value in there easily.

that's ok, unless i need to access it in a trigger.

Why can't you grab it out of the temp table from the trigger?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Peter Eisentraut wrote:

sad writes:

The first problem using temp table is:

CREATE TABLE locals (name text, value text);

CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';

SELECT test_locals_access() ;

column
-------------
<null>

CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');

SELECT test_locals_access() ;

column
-------------
<null> <=== the Function seing global table

This is a problem in plpgsql, not in temporary tables.

Yes, see the FAQ. You have to use EXECUTE for temp table access in
functions.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgman@candle.pha.pa.us (Bruce Momjian) wrote in message news:<200309032042.h83KgmJ24665@candle.pha.pa.us>...

Yes, see the FAQ. You have to use EXECUTE for temp table access in
functions.

Unfortunately in the 7.2.x version I have available, EXECUTE does not
allow "select into". The docs suggests a technique for using a FOR
loop to extract information from an EXECUTE, but the technique only
works for finite set of expected variables, not for arbitrary values.

Any suggestions on how to kludge this issue? It would be of great
value to me if there were a way to set and retrieve just one global
variable that persists throughout a session, and which can be
retrieved from a function.

miko@idocs.com (Miko O'Sullivan) writes:

Unfortunately in the 7.2.x version I have available, EXECUTE does not
allow "select into". The docs suggests a technique for using a FOR
loop to extract information from an EXECUTE, but the technique only
works for finite set of expected variables, not for arbitrary values.

How do you figure that? AFAICS "FOR" with a record variable as target
will cover any case that SELECT INTO could handle.

regards, tom lane

On Wed, 17 Sep 2003 17:23:05 -0400, Tom Lane wrote

miko@idocs.com (Miko O'Sullivan) writes:

Unfortunately in the 7.2.x version I have available, EXECUTE does not
allow "select into". The docs suggests a technique for using a FOR
loop to extract information from an EXECUTE, but the technique only
works for finite set of expected variables, not for arbitrary values.

How do you figure that? AFAICS "FOR" with a record variable as
target will cover any case that SELECT INTO could handle.

Tom: I don't "figure" it, it's just all I could get from documentation and
discussion that is rather sparse in concrete examples. For example, this
thread includes two postings that indicate that session variables can be
done, and make references to commands, but fail to actually show how to do
them. The documentation is the same way. The one example for FOR loops is
confusing and overly broad. It fail to step through the example code clearly
showing where a value from a temp table is extracted and stored in a variable.

I certainly don't mean to be critical, because PostGres is a fantastic
product. However, we in the open source community need to learn that
software is only as good as its documentation. Concrete examples are the
heart of good technical documentation. I would go so far to say that any
technical documentation w/o simple concrete examples demonstrating each point
is at best a quarter as useful as it might be.

I guess I should live by my own words, so for concrete examples of concrete
examples, please see any of the pages in the Idocs Guide to HTML. There are
links at http://www.idocs.com.

-Miko

Miko O'Sullivan
Programmer Analyst
Rescue Mission of Roanoke