Re: Sharing data between stored functions?

From: Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, inspector morse <inspectormorse86(at)gmail(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Sharing data between stored functions?
Date: 2015-03-05 19:15:55
Message-ID: 54F8AB6B.9090509@BlueTreble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 3/5/15 1:05 PM, Adrian Klaver wrote:
>> Is there any reason why Postgresql does not implement the SQL standard's
>> version of GLOBAL temporary tables?

Because no one has gotten around to it. There's been discussion about it
(search the pgsql-hackers archives).

>> I also don't like the idea of passing the "state" around to functions.
>> I'd rather just query a table OR read some global variable. The JSON
>> /HSTORE syntax looks horrible, I just like to stick with the simplicity
>> of a table.
>
> So create a permanent session table and assign session keys. Strangely
> enough that is one of the ways Django(web framework does it):) I know
> you want to build your set up, but this is a solved problem. It is
> solved because people ran into the issues you already have encountered
> and are yet to encounter and realized that common problems led to common
> solutions. Given that you want to reinvent the wheel I would suggest
> spending time looking at the other wheels out there and borrow from them.

At this point I'm confused as to whether you want something that spans
multiple sessions or not.

If you want something that goes beyond one backend session (but perhaps
persists for the length of a web session) then do what Adrian suggested.

If you only want something that lasts as long as a Postgres backend
session then another option is to use the global hash that pl/perl
provides. Despite possible confusion from it's name, it's only global
for each Postgres backend. You can store things in there and they will
be accessible for all functions. You could keep an array of composite
types (records) in there and that would act pretty much like a table.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message wambacher 2015-03-05 19:21:32 Re: autovacuum worker running amok - and me too ;)
Previous Message wambacher 2015-03-05 19:11:43 Re: autovacuum worker running amok - and me too ;)