Thread: Problem with plpgsql function

Hi All, 
I've been bangin away on a 7.4.x based database server trying to get a
plpgsql function to work the way I'm expecting it to. I've used various
resourced on the net to come up with the function, and as far as I can
tell it's "in proper form", but I've got the feeling that I'm missing
something.
I've created a new data type called:
CREATE TYPE account_info AS (username text, password text);
With that I want to return multiple rows, based on the results of the
function, using the SETOF and rowtype declarations, such that the
function looks like:

CREATE OR REPLACE FUNCTION get_account_info(text) RETURNS SETOF
account_info AS 'DECLARE acc account_info%rowtype; domain_name ALIAS FOR $1; company_id RECORD;
BEGIN acc.username := NULL; acc.password := NULL;
 SELECT INTO company_id cs.id, to_char(cs.id, ''FM0999'') AS cid FROM
virtual_host vh   LEFT JOIN virtual_machine vm ON (vm.id = vh.vm_id)   LEFT JOIN company_summary cs ON (cs.id =
vm.company_id) WHERE vh.domain_name = domain_name;
 
 FOR acc IN EXECUTE ''SELECT '''' || company_id.cid || '''' || c.id,
a.password FROM company_summary cs                             LEFT JOIN contact c ON (c.company_id =
cs.id)                             LEFT JOIN company_'' || company_id.cid ||
''.account a ON (a.contact_id = c.id)                             WHERE cs.id = '' || company_id.id LOOP  RETURN NEXT
acc;END LOOP;
 
 RETURN;END;
' LANGUAGE plpgsql;
According to the system, the function is created with out issue, and
there appear to not be any syntax errors being returned, however when I
execute the function in the query like this:

select get_account_info('test.com');
I get this error:

ERROR:  set-valued function called in context that cannot accept a set

The backend logger results look like:

Jan 15 13:42:56 jupiter 5439[3164]: [128-1] LOG:  query: select
get_account_info('test.com');
Jan 15 13:42:56 jupiter 5439[3164]: [129-1] LOG:  query: SELECT  NULL
Jan 15 13:42:56 jupiter 5439[3164]: [129-2] CONTEXT:  PL/pgSQL function
"get_account_info" line 7 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [130-1] LOG:  query: SELECT  NULL
Jan 15 13:42:56 jupiter 5439[3164]: [130-2] CONTEXT:  PL/pgSQL function
"get_account_info" line 8 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [131-1] LOG:  query: SELECT  cs.id,
to_char(cs.id, 'FM0999') AS cid FROM virtual_host vh LEFT JOIN
virtual_machine vm ON (vm.id =
Jan 15 13:42:56 jupiter 5439[3164]: [131-2]  vh.vm_id) LEFT JOIN
company_summary cs ON (cs.id = vm.company_id) WHERE vh.domain_name =  $1
Jan 15 13:42:56 jupiter 5439[3164]: [131-3] CONTEXT:  PL/pgSQL function
"get_account_info" line 10 at select into variables
Jan 15 13:42:56 jupiter 5439[3164]: [132-1] LOG:  query: SELECT  'SELECT
'' || company_id.cid || '' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[132-2]                               LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-3]                               LEFT JOIN company_' ||  $1  ||
'.account a ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-4]                               WHERE cs.id = ' ||  $2
Jan 15 13:42:56 jupiter 5439[3164]: [132-5] CONTEXT:  PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [133-1] LOG:  query: SELECT ' ||
company_id.cid || ' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[133-2]                               LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-3]                               LEFT JOIN company_0011.account a
ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-4]                               WHERE cs.id = 11
Jan 15 13:42:56 jupiter 5439[3164]: [133-5] CONTEXT:  PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [134-1] ERROR:  set-valued function
called in context that cannot accept a set
Jan 15 13:42:56 jupiter 5439[3164]: [134-2] CONTEXT:  PL/pgSQL function
"get_account_info" line 20 at return next
Can anyone see anything that I missed? Or has any suggestions?

-- 
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services

Chris Bowlby wrote:
> select get_account_info('test.com');
> 
>  I get this error:
> 
> ERROR:  set-valued function called in context that cannot accept a set

This is the "classic" SRF error -- you need to use an SRF like a 
relation in the FROM clause, so do this instead:
  select * FROM get_account_info('test.com');

HTH,

Joe

Ack, I knew it was something small, I was under the impression that I
had been using that, it just took someone to point it out to make me
look again :>... thanks..

On Thu, 2004-01-15 at 14:47, Joe Conway wrote:
> Chris Bowlby wrote:
> > select get_account_info('test.com');
> > 
> >  I get this error:
> > 
> > ERROR:  set-valued function called in context that cannot accept a set
> 
> This is the "classic" SRF error -- you need to use an SRF like a 
> relation in the FROM clause, so do this instead:
> 
>    select * FROM get_account_info('test.com');
> 
> HTH,
> 
> Joe
-- 
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services