[SQL] Stored procedures

[beyaRecords - The home Urban music]

Hi,
I am having problems with a stored procedure (plpgsql) that takes in a 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 = customerID;
return rec;
end
'
language 'plpgsql';

I am calling the procedure as follows:

select clientRec('tmpg60');

I am getting the following error: 

ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "pg_clientRec" while casting return value to function's return type

What am I doing wrong


regards

Uzo

Re: [SQL] Stored procedures

[Tom Lane]

beyaRecords - The home Urban music <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly