Re: functions and temporary tables

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