Re: functions and temporary tables

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