Re: Stored Procedures returning a RECORD

--- Kent Anderson <kenta(at)ezyield(dot)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