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
>
>
>