Subject: Problem with plpgsql function
From: Chris Bowlby <excalibur@hub.org>
Reply-To: chris@hub.org
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Hub.Org Networking Services
Message-Id: <1074191967.33449.11.camel@morpheus.hub.org>
Mime-Version: 1.0
Date: Thu, 15 Jan 2004 14:39:27 -0400
Content-Transfer-Encoding: 7bit

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