X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CEFCB475F8C; Thu, 13 Feb 2003 20:36:02 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id B6DCF475D91; Thu, 13 Feb 2003 20:29:03 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 14 Feb 2003 09:44:10 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A6E@VISION1> From: Michael Weaver To: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org Subject: Returning result sets. Date: Fri, 14 Feb 2003 09:44:09 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2D3CA.9109FA80" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/164 X-Sequence-Number: 6180 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C2D3CA.9109FA80 Content-Type: text/plain; charset="iso-8859-1" How do I declare a stored function to return a result set. (ie set of records) I can define a function returning " setof record " but I get an error: A column definition list is required for functions returning RECORD The docs(7.3) seem to think that this should be ok. so what is wrong? I have managed to get it to work by first declaring a type for the record set: CREATE TYPE sp_report_retail_sales_type AS (a int8, b int8, c numeric, d numeric); TIA, Mike Weaver Software Developer 5, 42 Ladner Street O'Connor, WA, 6163 All correspondence: PO Box Y3502 East St Georges Terrace Perth WA 6832 P: (+618) 9331 2700 F: (+618) 9331 3733 M: 0403 385 181 W: http://www.corpusglobe.com/ E: mweaver@corpusglobe.com This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You. ------_=_NextPart_001_01C2D3CA.9109FA80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Returning result sets.

How do I declare a stored function to return a result set= . (ie set of records)

I can define a function returning " setof record &qu= ot;
but I get an error:

A column definition list is required for functions return= ing RECORD

The docs(7.3)  seem to think that this should be ok.=
so what is wrong?

I have managed to get it to work by first declaring a typ= e for the record set:

CREATE TYPE sp_report_retail_sales_type AS (a int8, b int= 8, c numeric, d numeric);

TIA,

Mike Weaver
Software Developer

5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832

P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: mweaver@corpusglobe.com

This email is intended only for the use of the individual= or entity named above and may contain information that is confidential. If= you are not the intended recipient, you are hereby notified that any disse= mination, distribution or copying of this email is strictly prohibited. Whe= n addressed to our clients, any opinions or advice contained in this email = are subject to the terms and conditions expressed in the governing Corpus G= lobe client engagement letter. If you have received this Email in error, pl= ease notify us immediately by return email or telephone +61 8 9331 2700 and= destroy the original message. Thank You.


------_=_NextPart_001_01C2D3CA.9109FA80--