From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: Kent Anderson <kenta@ezyield.com>
To: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Re: Stored Procedures returning a RECORD
Date: Tue, 5 Oct 2004 13:41:58 -0700 (PDT)
Message-ID: <20041005204158.92786.qmail@web20822.mail.yahoo.com> (raw)
In-Reply-To: <LPENJIOOLAIJBFKIBDKOOECKFBAD.kenta@ezyield.com>
--- Kent Anderson <kenta@ezyield.com> wrote:
> 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'
Your return value has to be of a specific type
("record" is too generic). Every table automatically
has a type created, which each of its rows conforms
to. Or you could create a custom type, using "create
type".
>
> 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.
>
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff_eckermann@yahoo.com, kenta@ezyield.com
Subject: Re: Stored Procedures returning a RECORD
In-Reply-To: <20041005204158.92786.qmail@web20822.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox