Thread: select into temp tables withough using EXECUTE in plpgsql

I have a query surrounding somthing taht seems to have been a relatively
FAQ. It concerns the use of temporary tables in plpgsql.

Which initially resulted in the good old 'oid xxxx not found' error.

So I learnt from the maliing-list that I should be 'executing' (with EXECUTE
Command) my queries because expressions used in a PL/pgSQL function are only
prepared and saved once (without using execute).

However I would like to select ditinct valuse in my temp table which seem
impossible as:

" SELECT INTO is not currently supported within EXECUTE. So, the only way to
extract a result from a dynamically-created SELECT is to use the FOR ...
EXECUTE form described later."

(http://www.postgresql.org/docs/7.1/static/plpgsql-description.html)

I either cannot find or do not understand the documentation for this " FOR
... EXECUTE form " being described somewhere later in the docuanetation.

So to recap I'm creating a temp table fine.
I'm EXCUTING an insert into the temp table fine.
Then I cannot select from the table
in the form:

SELECT INTO int1 count(distinct(value)) from TEMP1;

Then it happily drops the table (without the select).

Any advice on how to select from a temp table into a variable wuold be
gratefully recieved.

Many Thanks

Edmund

Edmund Kleiser wrote:
>
> So to recap I'm creating a temp table fine.
> I'm EXCUTING an insert into the temp table fine.
> Then I cannot select from the table
> in the form:
>
> SELECT INTO int1 count(distinct(value)) from TEMP1;

The following creates a table, populates it and selects from it using an
EXECUTE. Does that help?

BEGIN;

CREATE TABLE exectest (a integer, b text, PRIMARY KEY (a));
COPY exectest FROM stdin;
1   aaa
2   bbb
3   ccc
\.

CREATE FUNCTION demo_exec_fn() RETURNS boolean AS '
DECLARE
     r     RECORD;
BEGIN
     FOR r IN EXECUTE ''SELECT * FROM exectest''
     LOOP
         RAISE NOTICE ''a=%, b=%'', r.a, r.b;
     END LOOP;
     RETURN true;
END
' LANGUAGE plpgsql;

SELECT demo_exec_fn();

COMMIT;

--
   Richard Huxton
   Archonet Ltd