Re: select into temp tables withough using EXECUTE in plpgsql - Mailing list pgsql-general

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