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 <[EMAIL PROTECTED]>
Hub.Org Networking Services
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
