pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting session global variables
4+ messages / 3 participants
[nested] [flat]
* Setting session global variables
@ 2001-05-17 00:43 Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
0 siblings, 1 reply; 4+ messages in thread
From: Jonathan Bartlett @ 2001-05-17 00:43 UTC (permalink / raw)
To: pgsql-sql
Is there a way to set a session global variable in PostgreSQL? The only
thing I see are examples of setting config variables. What I would like
to do is this:
Have a table "insertedby" or something that has
created_object oid,
created_by_user oid,
creation_date datetime
Then have a procedure, so that any table that wanted to record who
inserted which record could just use that procedure as an insert
trigger. The trigger would simply read the "current_user" session
variable (which the client had presumably set upon connection) and use it
to populate the "created_by_user" field.
However, the only way I can see to implement this is to have session
global variables. Is there a way to fake session global variables with a
table? Any comments would be appreciated.
Jon
johnnyb6@sdf.lonestar.org
SDF Public Access UNIX System - http://sdf.lonestar.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Setting session global variables
@ 2001-05-17 01:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2001-05-17 01:12 UTC (permalink / raw)
To: Jonathan Bartlett <johnnyb6@SDF.lonestar.org>; +Cc: pgsql-sql
Jonathan Bartlett <johnnyb6@SDF.lonestar.org> writes:
> However, the only way I can see to implement this is to have session
> global variables. Is there a way to fake session global variables with a
> table? Any comments would be appreciated.
You could do it with temp tables: the same temp table name would refer
to a different table in each session.
(You realize, of course, that CURRENT_USER already exists per SQL spec.
I assume you just meant that you'd like to have things *like*
CURRENT_USER, but defined by yourself...)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Setting session global variables
@ 2001-05-17 02:48 Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Jonathan Bartlett @ 2001-05-17 02:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Yes, that's perfect! Actually, I was thinking of a real user, but more
in the fashion of "pretend" users. If you've ever used Oracle apps, it
would be like an apps user, not the user "apps".
Anyway, that's an excellent idea. Thank you much!
Jon
johnnyb6@sdf.lonestar.org
SDF Public Access UNIX System - http://sdf.lonestar.org
On Wed, 16 May 2001, Tom Lane wrote:
> Jonathan Bartlett <johnnyb6@SDF.lonestar.org> writes:
> > However, the only way I can see to implement this is to have session
> > global variables. Is there a way to fake session global variables with a
> > table? Any comments would be appreciated.
>
> You could do it with temp tables: the same temp table name would refer
> to a different table in each session.
>
> (You realize, of course, that CURRENT_USER already exists per SQL spec.
> I assume you just meant that you'd like to have things *like*
> CURRENT_USER, but defined by yourself...)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Setting session global variables
@ 2001-05-17 14:57 Kyle <kyle@actarg.com>
0 siblings, 0 replies; 4+ messages in thread
From: Kyle @ 2001-05-17 14:57 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
>
> Is there a way to set a session global variable in PostgreSQL? The
> only
> thing I see are examples of setting config variables.
Not sure if session variables are your best solution, but here's how you
implement them:
-- Warning: this defines a global variable in the tcl interpretor. This
could
-- crash with other TCL procedures if the same name were used during the
same connection.
-- Store a value in a variable. This is helpful for caching values in a
transaction
-- Calling sequence: store(variable,value)
create function store(text,text) returns int4 as '
global store_vars
return [set store_vars($1) $2]
' LANGUAGE 'pltcl';
-- Fetch a value from a variable.
-- Calling sequence: recall(variable)
create function recall(text) returns int4 as '
global store_vars
return [subst $store_vars($1)]
' LANGUAGE 'pltcl';
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-05-17 14:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-17 00:43 Setting session global variables Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
2001-05-17 01:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-17 02:48 ` Jonathan Bartlett <johnnyb6@SDF.lonestar.org>
2001-05-17 14:57 Setting session global variables Kyle <kyle@actarg.com>
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