functions and temporary tables

[anorakgirl]

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

[Jan Poslusny]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[anorakgirl]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"anorakgirl" <po******@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings