Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0C5B14768AF for ; Wed, 29 Jan 2003 08:24:47 -0500 (EST) Received: from guppy.vub.ac.be (guppy.vub.ac.be [134.184.129.2]) by postgresql.org (Postfix) with ESMTP id 6FE394767C2 for ; Wed, 29 Jan 2003 08:24:34 -0500 (EST) Received: from mach.vub.ac.be (mach.vub.ac.be [134.184.129.3]) by guppy.vub.ac.be (8.9.1b+Sun/3.17.1.ap (guppy)) id OAA06103; Wed, 29 Jan 2003 14:24:32 +0100 (MET) for Received: from ulb.ac.be (geog-pc35.ulb.ac.be [164.15.134.155]) by mach.vub.ac.be (8.9.3/3.13.3.ap (mach)) id OAA01122; Wed, 29 Jan 2003 14:24:30 +0100 (MET) for Received: from 164.15.128.4 (SquirrelMail authenticated user mlennert) by http://moritz.homelinux.org with HTTP; Wed, 29 Jan 2003 14:24:22 +0100 (CET) Message-ID: <18395.164.15.128.4.1043846662.squirrel@http://moritz.homelinux.org> In-Reply-To: <3E37D073.7CA9BD80@rodos.fzk.de> References: <3E37D073.7CA9BD80@rodos.fzk.de> Date: Wed, 29 Jan 2003 14:24:22 +0100 (CET) Subject: Re: plpgsql: return results of a dynamic query From: "Moritz Lennert" To: "Christoph Haller" Cc: pgsql-sql@postgresql.org Reply-To: mlennert@club.worldonline.be X-Mailer: SquirrelMail (version 1.3.2) MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/485 X-Sequence-Number: 11350 >> >> 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