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