pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with plpgsql function
3+ messages / 2 participants
[nested] [flat]
* Problem with plpgsql function
@ 2004-01-15 18:39 Chris Bowlby <excalibur@hub.org>
0 siblings, 1 reply; 3+ messages in thread
From: Chris Bowlby @ 2004-01-15 18:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with plpgsql function
@ 2004-01-15 18:47 Joe Conway <mail@joeconway.com>
parent: Chris Bowlby <excalibur@hub.org>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2004-01-15 18:47 UTC (permalink / raw)
To: chris@hub.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with plpgsql function
@ 2004-01-15 19:06 Chris Bowlby <excalibur@hub.org>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Chris Bowlby @ 2004-01-15 19:06 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: chris@hub.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-15 19:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-15 18:39 Problem with plpgsql function Chris Bowlby <excalibur@hub.org>
2004-01-15 18:47 ` Joe Conway <mail@joeconway.com>
2004-01-15 19:06 ` Chris Bowlby <excalibur@hub.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox