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