Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0350t738686 for ; Wed, 3 Jan 2001 00:00:55 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f0351BB15215; Wed, 3 Jan 2001 00:01:11 -0500 (EST) To: Brett Schwarz cc: "'pgsql-sql@postgresql.org'" Subject: Re: FUNCTION returing multiple rows In-reply-to: <72660A24B978D411BB8A00B0D03DFE0115117D@misty.packetvideo.com> References: <72660A24B978D411BB8A00B0D03DFE0115117D@misty.packetvideo.com> Comments: In-reply-to Brett Schwarz message dated "Tue, 02 Jan 2001 13:15:12 -0800" Date: Wed, 03 Jan 2001 00:01:11 -0500 Message-ID: <15212.978498071@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200101/26 X-Sequence-Number: 687 Brett Schwarz writes: > However, when I look at the programmers guide (Ch. 4), it almost leads me to > believe otherwise: > The following more interesting example takes a single argument of type EMP, > and retrieves multiple results: > select function hobbies (EMP) returns set of HOBBIES > as 'select (HOBBIES.all) from HOBBIES > where $1.name = HOBBIES.person' > language 'sql'; This bit of the docs seems not to have been updated since PostQuel days. The ".all" syntax is definitely out of date --- ".*" is the SQL equivalent. There is a descendant of this example in the current regression tests: CREATE FUNCTION hobbies(person) RETURNS setof hobbies_r AS 'select * from hobbies_r where person = $1.name' LANGUAGE 'sql'; (Note that `person' is being used in two ways here, first as a datatype and then as a field name --- not the least confusing example I could have thought of...) > Also, is there any type of function return multiple rows (plpgsql, pltcl, > etc)? In 7.0 and prior releases, *only* SQL functions can return multiple rows. The 7.1 function manager removes that hardwired assumption, but in 7.1 the plpgsql, pltcl, plperl function language handlers don't take advantage of the capability --- so the current state of affairs is that you can do it in SQL or C functions only. Hopefully this will be improved in future releases. regards, tom lane