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 92040329E84 for ; Tue, 5 Oct 2004 18:53:47 +0100 (BST) 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 96159-01 for ; Tue, 5 Oct 2004 17:53:43 +0000 (GMT) Received: from ezyield.com (unknown [209.164.30.206]) by svr1.postgresql.org (Postfix) with ESMTP id 2565432A10B for ; Tue, 5 Oct 2004 18:53:42 +0100 (BST) Received: from localhost (xolinux73g2 [127.0.0.1]) by ezyield.com (8.12.5/8.11.6) with ESMTP id i95KoLeW000572 for ; Tue, 5 Oct 2004 13:50:21 -0700 Received: from ezyield.com ([127.0.0.1]) by localhost (mail.ezyield.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32194-10 for ; Tue, 5 Oct 2004 13:50:21 -0700 (PDT) Received: from KENTA (67.108.170.254.ptr.us.xo.net [67.108.170.254]) by ezyield.com (8.12.5/8.11.6) with SMTP id i95KnKEd000488 for ; Tue, 5 Oct 2004 13:49:22 -0700 From: "Kent Anderson" To: "Pgsql-Sql@Postgresql. Org" Subject: Stored Procedures returning a RECORD Date: Tue, 5 Oct 2004 13:52:53 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0002_01C4AAE2.9C863B30" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new 20040701 at ezyield.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.6 tagged_above=0.0 required=5.0 tests=FORGED_MUA_OIMO, HTML_30_40, HTML_MESSAGE, HTML_TAG_EXISTS_TBODY X-Spam-Level: *** X-Archive-Number: 200410/32 X-Sequence-Number: 19135 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C4AAE2.9C863B30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I am attempting to use a stored procedure to pull a report from the database. My questions is if its even possible to pull the data using a function and then treat the returned data as a normal recordset with the web pages. The actual function looks like CREATE OR REPLACE FUNCTION submissionreport(integer, date, text) RETURNS RECORD AS' DECLARE somekey ALIAS for $1; somedate ALIAS for $2; somesortorder ALIAS for $3; submission RECORD; BEGIN SELECT INTO submission (... stuff goes here) ORDER BY sort; RETURN submission; END; ' LANGUAGE plpgsql; The error being returned is in the web pages" SQLState: 08S01 Native Error Code: 1 Error while executing the query; ERROR: cannot display a value of type record" Kent Anderson EZYield.com 407-629-0900 www.ezyield.com ======================================== This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field. ------=_NextPart_000_0002_01C4AAE2.9C863B30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I am atte= mpting to=20 use a stored procedure to pull a report from the database. My questions is = if=20 its even possible to pull the data using a function and then trea= t the=20 returned data as a normal recordset with the web pages.<= /DIV>

The actua= l function=20 looks like

CREATE OR= REPLACE=20 FUNCTION submissionreport(integer, date, text) RETURNS RECORD=20 AS'

DECLARE
somekey ALIAS for $1;
some= date=20 ALIAS for $2;
somesortorder ALIAS for $3;
submission=20 RECORD;
BEGIN

SELECT IN= TO=20 submission (... stuff goes here)
ORDER BY sort;

&nb= sp; =20 RETURN submission;
END;
' LANGUAGE plpgsql;

The=20 error being returned is in the web pages"

SQLState: 08S01 Native Error Code: 1 Error while ex= ecuting=20 the query; ERROR: cannot display a value of type record"

Kent Anderson

EZYield.com

407-629-0900

www.ezyield.com

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

This electronic message transmission conta= ins=20 information from the Company that may be proprietary, confidential and/or= =20 privileged. The information is intended only for the use of the individual(= s) or=20 entity named above. If you are not the intended recipient, be aware t= hat=20 any disclosure, copying or distribution or use of the contents of this=20 information is prohibited. If you have received this electronic=20 transmission in error, please notify the sender immediately by replying to = the=20 address listed in the "From:" field.

------=_NextPart_000_0002_01C4AAE2.9C863B30--