pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsession variable
12+ messages / 7 participants
[nested] [flat]
* session variable
@ 2003-09-01 14:11 sad <sad@bankir.ru>
0 siblings, 1 reply; 12+ messages in thread
From: sad @ 2003-09-01 14:11 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-01 16:57 Bruce Momjian <pgman@candle.pha.pa.us>
parent: sad <sad@bankir.ru>
0 siblings, 2 replies; 12+ messages in thread
From: Bruce Momjian @ 2003-09-01 16:57 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-02 01:28 Bupp Phillips <hello@noname.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Bupp Phillips @ 2003-09-02 01:28 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-02 12:29 sad <sad@bankir.ru>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 12+ messages in thread
From: sad @ 2003-09-02 12:29 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-02 12:40 Andreas Pflug <pgadmin@pse-consulting.de>
parent: sad <sad@bankir.ru>
1 sibling, 1 reply; 12+ messages in thread
From: Andreas Pflug @ 2003-09-02 12:40 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [BUGS] session variable
@ 2003-09-02 13:13 sad <sad@bankir.ru>
parent: Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 1 reply; 12+ messages in thread
From: sad @ 2003-09-02 13:13 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-02 22:51 Andreas Pflug <pgadmin@pse-consulting.de>
parent: Bupp Phillips <hello@noname.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andreas Pflug @ 2003-09-02 22:51 UTC (permalink / raw)
To: Bupp Phillips <hello@noname.com>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [BUGS] session variable
@ 2003-09-03 17:40 Peter Eisentraut <peter_e@gmx.net>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 12+ messages in thread
From: Peter Eisentraut @ 2003-09-03 17:40 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: session variable
@ 2003-09-03 17:55 Bruce Momjian <pgman@candle.pha.pa.us>
parent: sad <sad@bankir.ru>
1 sibling, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2003-09-03 17:55 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [BUGS] session variable
@ 2003-09-03 20:42 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 2003-09-03 20:42 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: sad <sad@bankir.ru>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [BUGS] session variable
@ 2003-09-13 18:23 Miko O'Sullivan <miko@idocs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: miko@idocs.com @ 2003-09-13 18:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [BUGS] session variable
@ 2003-09-17 21:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Miko O'Sullivan <miko@idocs.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2003-09-17 21:23 UTC (permalink / raw)
To: miko@idocs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-09-17 21:23 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-01 14:11 session variable sad <sad@bankir.ru>
2003-09-01 16:57 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-02 01:28 ` Bupp Phillips <hello@noname.com>
2003-09-02 22:51 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-09-02 12:29 ` sad <sad@bankir.ru>
2003-09-02 12:40 ` Andreas Pflug <pgadmin@pse-consulting.de>
2003-09-02 13:13 ` sad <sad@bankir.ru>
2003-09-03 17:40 ` Peter Eisentraut <peter_e@gmx.net>
2003-09-03 20:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-13 18:23 ` Miko O'Sullivan <miko@idocs.com>
2003-09-17 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-03 17:55 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox