Re: [GENERAL] select into temp tables withough using EXECUTE in plpgsql
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] 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 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
