469,106 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
4 15426
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

Nov 22 '05 #2
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

Nov 22 '05 #3
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

Nov 22 '05 #4
"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

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Peter Gorelczenko | last post: by
reply views Thread by Zlatko Matić | last post: by
1 post views Thread by Stefan van Roosmalen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.