Re: functions and temporary tables

Hi,
when I solved a very similar problem and I finally left idea about
temporary tables and I used something as following:
- create some permanent table(s) to store connection-specific
informations with added column 'pid' (which is primary key).
- when you insert some row into this table, use pg_backend_pid() as
primary key
- when you select propper row, use clause 'where pid = pg_backend_pid()'
- be carefull about "dead" rows (it's pid does not correspond with
existing pg backend yet)

regards,
pajout

anorakgirl wrote:

>hi,
>
>i'm writing some plpgsql functions which use a temporary table, and i've
>read the FAQ and am using EXECUTE to create and insert into my table to
>avoid errors caused by postgres caching the query plan. however, i can't
>work out how to get the data back out of my temporary table, as i don't
>think i can get the results of a select performed using EXECUTE? if i just
>do the select directly, once the temporary table has been recreated, the
>select fails with the error "relation with OID xxxxx does not exist". Can
>anyone suggest how I can void this and get data back out of my temp table?
>
>I've pasted the functions at the end of the mail if it helps.
>Thanks,
>Tamsin
>
>CREATE OR REPLACE FUNCTION setAppUser (TEXT) RETURNS BOOLEAN AS '
> DECLARE
> uname alias for $1;
>
> BEGIN
> IF isTable(''app_user'') THEN
> EXECUTE ''DELETE FROM app_user'';
> ELSE
> EXECUTE ''CREATE TEMPORARY TABLE app_user (username VARCHAR(50)) ON
>COMMIT DROP'';
> END IF;
> EXECUTE ''INSERT INTO app_user VALUES (''''''||uname||'''''')'';
>
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
> --RETURNS THE APP USERNAME IF THERE IS ONE
> CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
> DECLARE
> user_record RECORD;
>
> BEGIN
> IF isTable(''app_user'') THEN
> SELECT INTO user_record * FROM app_user;
> IF NOT FOUND THEN
> RETURN '''';
> ELSE
> RETURN user_record.username;
> END IF;
> ELSE
> RETURN '''';
> END IF;
> END;
> ' LANGUAGE 'plpgsql';
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>