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