Thread: plpgsql: return results of a dynamic query
Hello, I'am a complete newbie to plpgsql, so if this information is explained somewhere where I haven't looked, yet, please point me to the doc... (I didn't find the answer in the PostgreSQL Programmer's Guide.) I use PG 7.3.1. I'm trying to retrieve a row count from several tables (40) and would like to create a function that does this automatically for the 40 and displays the results. So, I loop through the tables: DECLARE obj RECORD; BEGIN FOR obj IN SELECT relname AS name FROM pg_class WHERE relkind IN ('r') AND relname like '%_random' AND relname != 'tout_random' LOOP then I need to do the select count for each table in the lines of SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE; and return the result of each of these select counts. Could someone indicate how to return the results of these queries ? Am I right that in order to do this dynamic query, I have to use an EXECUTE statement ? Can I return the results of an EXECUTE statement ? Thanks, Moritz
> > I'm trying to retrieve a row count from several tables (40) and would like > to create a function that does this automatically for the 40 and displays > the results. So, I loop through the tables: > > DECLARE > obj RECORD; > BEGIN > FOR obj IN SELECT relname AS name FROM pg_class > WHERE relkind IN ('r') > AND relname like '%_random' AND relname != 'tout_random' > LOOP > > > then I need to do the select count for each table in the lines of > > SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN tout_random > AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE; > > and return the result of each of these select counts. > > Could someone indicate how to return the results of these queries ? > Am I right that in order to do this dynamic query, I have to use an > EXECUTE statement ? Can I return the results of an EXECUTE statement ? > You are on the right track. The documentation says: The results from SELECT queries are discarded by EXECUTE, and 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-IN-EXECUTE form described later. So something like the following should do the trick: DECLARE obj RECORD; obj2 RECORD; countresult BIGINT; BEGIN countresult := 0; FOR obj IN SELECT relname AS name FROM pg_class WHERE relkind IN ('r') ANDrelname like '%_random' AND relname != 'tout_random' LOOP FOR obj2 IN EXECUTE ''SELECT count(t1.id) AS total FROM '' || quote_ident(obj.name) || '' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE'' LOOP countresult := countresult+ obj2.total; END LOOP; END LOOP; RETURN countresult; END; ' LANGUAGE 'plpgsql' ; I doubt this is exactly what you wanted. It looks like you were asking for the results of every count. The only quick solution I can see for this is populate a table with the name and count of your 40 tables. Replace the "countresult := countresult + obj2.total;" line by INSERT INTO countresults VALUES ( obj.name , obj2.total ) ; and don't forget to reset the table before by DELETE FROM countresults ; I hope this helps for now. Regards, Christoph
>> >> I'm trying to retrieve a row count from several tables (40) and would > like >> to create a function that does this automatically for the 40 and > displays >> the results. So, I loop through the tables: >> >> DECLARE >> obj RECORD; >> BEGIN >> FOR obj IN SELECT relname AS name FROM pg_class >> WHERE relkind IN ('r') >> AND relname like '%_random' AND relname != 'tout_random' >> LOOP >> >> >> then I need to do the select count for each table in the lines of >> >> SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN > tout_random >> AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE; >> >> and return the result of each of these select counts. >> >> Could someone indicate how to return the results of these queries ? >> Am I right that in order to do this dynamic query, I have to use an >> EXECUTE statement ? Can I return the results of an EXECUTE statement ? > >> > You are on the right track. > The documentation says: > > The results from SELECT queries are discarded by EXECUTE, and 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-IN-EXECUTE form described later. > > So something like the following should do the trick: > > DECLARE > obj RECORD; > obj2 RECORD; > countresult BIGINT; > BEGIN > countresult := 0; > FOR obj IN SELECT relname AS name FROM pg_class > WHERE relkind IN ('r') > AND relname like '%_random' AND relname != 'tout_random' > LOOP > FOR obj2 IN > EXECUTE ''SELECT count(t1.id) AS total FROM '' || > quote_ident(obj.name) || > '' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE'' > LOOP > countresult := countresult + obj2.total; > END LOOP; > END LOOP; > RETURN countresult; > END; > ' LANGUAGE 'plpgsql' ; > > I doubt this is exactly what you wanted. > It looks like you were asking for the results of every count. > The only quick solution I can see for this is > populate a table with the name and count of your 40 tables. > > Replace the "countresult := countresult + obj2.total;" line by > INSERT INTO countresults VALUES ( obj.name , obj2.total ) ; > and don't forget to reset the table before by > DELETE FROM countresults ; > > I hope this helps for now. Thanks, that was it ! The only thing I had to change was the quoting in the EXECUTE statement (double quoting gave me a parser error). So here's the version that works for me: DECLARE obj RECORD; obj2 RECORD; BEGIN DELETE FROM how_many; FOR obj IN SELECT relname AS name FROM pg_class WHERE relkind IN ('r') AND relname like '%_random' AND relname != 'tout_random' LOOP FOR obj2 IN EXECUTE 'SELECT count(t1.id) AS total FROM ' || quote_ident(obj.name) || ' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE' LOOP INSERT INTO how_many VALUES ( obj.name , obj2.total ) ; END LOOP; END LOOP; RETURN true; END; Again thanks for the clear response. Cheers, Moritz
>> >> I'm trying to retrieve a row count from several tables (40) and would > like >> to create a function that does this automatically for the 40 and > displays >> the results. So, I loop through the tables: >> >> DECLARE >> obj RECORD; >> BEGIN >> FOR obj IN SELECT relname AS name FROM pg_class >> WHERE relkind IN ('r') >> AND relname like '%_random' AND relname != 'tout_random' >> LOOP >> >> >> then I need to do the select count for each table in the lines of >> >> SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN > tout_random >> AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE; >> >> and return the result of each of these select counts. >> >> Could someone indicate how to return the results of these queries ? >> Am I right that in order to do this dynamic query, I have to use an >> EXECUTE statement ? Can I return the results of an EXECUTE statement ? > >> > You are on the right track. > The documentation says: > > The results from SELECT queries are discarded by EXECUTE, and 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-IN-EXECUTE form described later. > > So something like the following should do the trick: > > DECLARE > obj RECORD; > obj2 RECORD; > countresult BIGINT; > BEGIN > countresult := 0; > FOR obj IN SELECT relname AS name FROM pg_class > WHERE relkind IN ('r') > AND relname like '%_random' AND relname != 'tout_random' > LOOP > FOR obj2 IN > EXECUTE ''SELECT count(t1.id) AS total FROM '' || > quote_ident(obj.name) || > '' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE'' > LOOP > countresult := countresult + obj2.total; > END LOOP; > END LOOP; > RETURN countresult; > END; > ' LANGUAGE 'plpgsql' ; > > I doubt this is exactly what you wanted. > It looks like you were asking for the results of every count. > The only quick solution I can see for this is > populate a table with the name and count of your 40 tables. > > Replace the "countresult := countresult + obj2.total;" line by > INSERT INTO countresults VALUES ( obj.name , obj2.total ) ; > and don't forget to reset the table before by > DELETE FROM countresults ; > > I hope this helps for now. Thanks, that was it ! The only thing I had to change was the quoting in the EXECUTE statement (double quoting gave me a parser error). So here's the version that works for me: DECLARE obj RECORD; obj2 RECORD; BEGIN DELETE FROM how_many; FOR obj IN SELECT relname AS name FROM pg_class WHERE relkind IN ('r') AND relname like '%_random' AND relname != 'tout_random' LOOP FOR obj2 IN EXECUTE 'SELECT count(t1.id) AS total FROM ' || quote_ident(obj.name) || ' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE' LOOP INSERT INTO how_many VALUES ( obj.name , obj2.total ) ; END LOOP; END LOOP; RETURN true; END; Again thanks for the clear response. Cheers, Moritz