X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5C56E52ABF for ; Sat, 23 Jul 2005 09:57:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93529-09 for ; Sat, 23 Jul 2005 12:57:48 +0000 (GMT) Received: from amanda.contactbda.com (ipn36372-e65122.cidr.lightship.net [216.204.66.226]) by svr1.postgresql.org (Postfix) with ESMTP id B746F529D3 for ; Sat, 23 Jul 2005 09:57:46 -0300 (ADT) Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2]) by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id j6NCvcI5016064; Sat, 23 Jul 2005 08:57:38 -0400 From: "Jim Buttafuoco" To: Tony Wasson Cc: "Mark R. Dingee" , pgsql-sql@postgresql.org Reply-To: jim@contactbda.com Subject: Re: Multi-column returns from pgsql Date: Sat, 23 Jul 2005 08:57:38 -0400 Message-Id: <20050723125711.M72454@contactbda.com> In-Reply-To: <6d8daee3050722111118944657@mail.gmail.com> References: <200507221149.21550.mark.dingee@cox.net> <20050722165558.M96628@contactbda.com> <6d8daee3050722111118944657@mail.gmail.com> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 192.168.1.1 (jim) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.156 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200507/208 X-Sequence-Number: 22186 or just return setof RECORD (version 7.4 +) ---------- Original Message ----------- From: Tony Wasson To: jim@contactbda.com Cc: "Mark R. Dingee" , pgsql-sql@postgresql.org Sent: Fri, 22 Jul 2005 11:11:09 -0700 Subject: Re: [SQL] Multi-column returns from pgsql > On 7/22/05, Jim Buttafuoco wrote: > > Mark, > > > > Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT rec; > > > > then your select statement would be > > select * from my_func() as (txt1 text,txt2 text); > > > > Jim > > Besides a simple RETURN NEXT, you'll need to return a SETOF some > composite type. You can do something like > > CREATE TYPE twotexts_t AS (txt1 TEXT, txt2 TEXT); > > CREATE OR REPLACE FUNCTION my_func() returns SETOF twotexts_t AS ' > DECLARE > rec record; > BEGIN > FOR rec IN SELECT txt1, txt2 FROM mytable LOOP > RETURN NEXT > END LOOP; > RETURN; > END;' language 'plpgsql'; > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend ------- End of Original Message -------