tgl@xxxxxxxxxxxxx wrote:
I hinted at a different approach in an earlier turn in this thread: I sketched only how you might handle the case where the session state is just a single value—by using a one-row, one-column temporary table with "on commit delete rows". But the general approach is to use a two column temporary table for key-value pairs. This approach is what the PG doc sketches here: « 43.13. Porting from Oracle PL/SQL Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead. » (That article of faith, "there are no packages and there never, ever will be", saddens me.) Because PG has no event trigger that fires on session creation (why is this?), I've settled on this optimistic pattern: begin insert into pg_temp.flag(val) values(true); exception when undefined_table then get stacked diagnostics msg = message_text; if msg != 'relation "pg_temp.flag" does not exist' then raise; else create temp table pg_temp.flag(val boolean not null) on commit delete rows; insert into pg_temp.flag(val) values(true); end if; end; The code would need to be more elaborate (and use "upsert") for key-value pairs. But that's easy to do. Do the experts on this list disapprove of this pattern and prefer (for a future regime) something like the Pavel Stehule scheme that Tom mentioned? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - [*] I didn't know that there was a PG doc policy sometimes not to call out a bad practice but, rather, to hide away (in an obscure backwater in the docs) the account of a feature that’s considered to be better avoided except in special cases. This effectively hides it from Google search (and similar) too because of the circular notion that few people find it, and fewer still publish pages that include the link,… and so on. I suppose that calling the thing an "option" while the doc for the "set" SQL statement uses the term of art "run-time parameter" is another “bad practice admonition by obscurity” notion. (I've referred to the thing as a "user-defined run-time parameter" in informal emails to colleagues. But that is a lot of syllables.) |
