Thread: functions and temporary tables
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';
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
>
>
>
On Mon, 9 Feb 2004, anorakgirl wrote:
> 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
IIRC, to get data out from an execute at this point requires using the FOR
record IN EXECUTE querystring LOOP ... END LOOP; construct.
Something like:
CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
DECLARE
user_record RECORD;
BEGIN
IF isTable(''app_user'') THEN
FOR user_record IN EXECUTE ''SELECT * FROM app_user'' LOOP
RETURN user_record.username;
END LOOP;
RETURN '''';
ELSE
RETURN '''';
END IF;
END;
' LANGUAGE 'plpgsql';
brilliant it works thank you!
(thanks for the alternate suggestion too pajout, i was just resigning
myself to rewriting everything using your method)
cheers,
tamsin
> On Mon, 9 Feb 2004, anorakgirl wrote:
>
>> 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
>
> IIRC, to get data out from an execute at this point requires using the
> FOR record IN EXECUTE querystring LOOP ... END LOOP; construct.
>
> Something like:
>
> CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
> DECLARE
> user_record RECORD;
>
> BEGIN
> IF isTable(''app_user'') THEN
> FOR user_record IN EXECUTE ''SELECT * FROM app_user'' LOOP
> RETURN user_record.username;
> END LOOP;
> RETURN '''';
> ELSE
> RETURN '''';
> END IF;
> END;
> ' LANGUAGE 'plpgsql';
"anorakgirl" <postgres@anorakgirl.co.uk> writes:
> ... 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?
At present you have to use a FOR IN EXECUTE loop, even if you only
expect one row out of the SELECT.
regards, tom lane