Re: spooky refusal to insert - Mailing list pgsql-general

Gurjeet Singh wrote:
> On 1/2/07, brian <brian@zijn-digital.com> wrote:
>
>>
>> Here are the new tables (Note that set_id() and get_id() are functions
>> which use the $_SHARED structure so that i can set some vars and refer
>> back to them later)
>>
>>
> Hi Brian,
>
>    Can you please explain the $_SHARED structure you mention here? I am not
> able to find documentation for it!!
>
>    If I understand it correctly, it probably is a structure stored in the
> backend, and the plpgsql functions can use it to store values across
> multiple calls; like C global variables.
>

Certainly. I'd been about to explain it further, however my initial cry
for help was already dragging on quite a bit so i left it at that.

$_SHARED is a global hash in PL/Perl. I found these two functions in the
docs:

39.4. Global Values in PL/Perl
http://www.postgresql.org/docs/8.2/static/plperl-global.html

You can find more info about using $_SHARED to communicate between
functions here:
http://www.oreillynet.com/pub/a/databases/2006/05/25/the-future-of-perl-in-postgresql.html

With these two functions, you pass the hash key you want to use, along
with the value you wish to store. The if() test is assigning it first,
so if, for some reason it cannot be set, if() will fail. You can, of
course, change the return value to anything you'd like. The original is
fine for my needs.

I generally use these to grab the current value of the sequence for some
look-up table as it is being added to. Things like sports teams,
countries, etc. Generally, tables with data that doesn't change a whole
lot, and are used primarily for relating between other tables.

So, i can use that later when i need to cross-reference some other
value. In this case, it was relating the arts_funders with specific arts
disciplines, as well as with funding types, as i added them to the database.

If you'd like a more concrete example, i'd be happy to send that.

/**
  * Store IDs from lookup tables
  *
  * @param  text    name  the hash key (lookup table name entry)
  * @param  int  val  the lookup table row id
  * @returns  text  success or not
  **/
CREATE OR REPLACE FUNCTION set_id(name TEXT val INT4) RETURNS TEXT AS
$$
if ($_SHARED{$_[0]} = $_[1])
{
   return 'ok';
}
else
{
   return "can't set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;


/**
  * Retrieve ID from lookup table
  *
  * @param  text    name  the hash key
  * @returns int the hash value
  **/
CREATE OR REPLACE FUNCTION get_id(name text) RETURNS INT4 IMMUTABLE AS
$$
   return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

Note that this last function will return NULL if the key does not exist
(which was the root of my problem, as the keys initially had extra
whitespace--oops!)

regards,
brian