Thread: Stored procedures

Hi,
I am having problems with a stored procedure (plpgsql) that takes in a=20=
=20
value and returns a record set.
my code is as follow:

create function pg_clientRec(text) setof record as
'
declare
customerID ALIAS $1;
rec record;

begin
    select into rec * from troubletickets where custID =3D customerID;
    return rec;
end
'
language 'plpgsql';

I am calling the procedure as follows:

select clientRec('tmpg60');

I am getting the following error:

ERROR:=A0=A0set-valued=A0function=A0called=A0in=A0context=A0that=A0cannot=
=A0accept=A0a=A0set
CONTEXT:=A0=A0PL/=20
pgSQL=A0function=A0"pg_clientRec"=A0while=A0casting=A0return=A0value=A0to=
=A0function's=A0r=20
eturn=A0type

What am I doing wrong????


regards

Uzo=

beyaRecords - The home Urban music <uzo@beya-records.com> writes:
> I am having problems with a stored procedure (plpgsql) that takes in a
> value and returns a record set.
> I am calling the procedure as follows:
> select clientRec('tmpg60');

Use 
select * from clientRec('tmpg60') as (column list);

If you declare the function as returning RECORD, you will need to
provide an AS clause that identifies the column set the records will
contain.  Without this, the parser has no idea what to expand "*" to.
See the example in section 7.2.1.4 here:
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html
        regards, tom lane