Thread: problem with pl/pgsql
I'm trying to build a table function using pl/pgsql. I've done this
successfully many times in the past, but this is the first time I've tried
to build up and execute a dynamic query, and either it or I (probably I)
am getting confused.
The error I get is:
foo=# select * from fetch_artistset_by_artists('{1}');
WARNING:Â Error occurred while executing PL/pgSQL functionfetch_artistset_by_artists
WARNING:Â line 30 at return next
ERROR:Â Attribute "r" not found
The stored proc is:
CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS
'DECLARE
artists alias for $1;
index integer := 1;
total integer := 0;
r record;
q text := ''select setID from (select setID,count(*) as c from artistSet where'';
BEGIN
WHILE artists[index] > 0
LOOP
if index > 1
then
q := q || '' or'';
end if;
q := q || '' artistID = '' || artists[index];
total := total + 1;
index := index + 1;
END LOOP;
if total = 0
then
RETURN;
end if;
q := q || '' group by setID) as foo where c = '' || total;
for r in execute q
LOOP
RETURN next r;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
The schema is:
create table artistSet
(
memberState integer not null,
setID integer not null references artistSetInfo(id),
artistID integer not null references artist(id),
primary key (setID, artistID)
);
Any thoughts? The error is on the "RETURN next r;" line, which has worked
fine for me in the past, unless I'm making a typo I'm just not seeing. So
that leads me to believe it has something to do with the dynamic query,
but I don't know what it would be.....
Ben,
I can't comment on your particular problem, but if you look through the
posts on this list, you will find that it's a bad idea to execute
dynamic queries which include input you can't control.
In your example this could lead to problems if you don't escape properly
the artist name. Consider the following artist name (take everything
literally, including the quotes):
'); delete from artistSet; --
Your function will end up executing the truncated select AND the delete,
ignoring the rest.
I'm not sure if the above "artist name" is properly escaped itself to
produce this result, but you get the idea.
The general recommendation is to use prepared queries when including
output from exterior, or at least do paranoid escaping.
HTH,
Csaba.
On Thu, 2003-04-17 at 07:40, Ben wrote:
> I'm trying to build a table function using pl/pgsql. I've done this
> successfully many times in the past, but this is the first time I've tried
> to build up and execute a dynamic query, and either it or I (probably I)
> am getting confused.
>
> The error I get is:
> foo=# select * from fetch_artistset_by_artists('{1}');
> WARNING:Â Error occurred while executing PL/pgSQL functionfetch_artistset_by_artists
> WARNING:Â line 30 at return next
> ERROR:Â Attribute "r" not found
>
> The stored proc is:
> CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS
> 'DECLARE
> artists alias for $1;
> index integer := 1;
> total integer := 0;
> r record;
> q text := ''select setID from (select setID,count(*) as c from artistSet where'';
> BEGIN
> WHILE artists[index] > 0
> LOOP
> if index > 1
> then
> q := q || '' or'';
> end if;
>
> q := q || '' artistID = '' || artists[index];
>
> total := total + 1;
> index := index + 1;
> END LOOP;
>
> if total = 0
> then
> RETURN;
> end if;
>
> q := q || '' group by setID) as foo where c = '' || total;
>
> for r in execute q
> LOOP
> RETURN next r;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
> The schema is:
> create table artistSet
> (
> memberState integer not null,
> setID integer not null references artistSetInfo(id),
> artistID integer not null references artist(id),
>
> primary key (setID, artistID)
> );
>
> Any thoughts? The error is on the "RETURN next r;" line, which has worked
> fine for me in the past, unless I'm making a typo I'm just not seeing. So
> that leads me to believe it has something to do with the dynamic query,
> but I don't know what it would be.....
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Hi, i think its really you whos confused *g* Am Don, 2003-04-17 um 07.40 schrieb Ben: <snip> > The stored proc is: > CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS > 'DECLARE > artists alias for $1; > index integer := 1; > total integer := 0; > r record; > q text := ''select setID from (select setID,count(*) as c from artistSet where''; <snip> ok, the proc returns a setof integer. <snip> > > for r in execute q > LOOP > RETURN next r; > END LOOP; > RETURN; > END; <snip> but this returns a record....so what you need here is return next r.setID; Hope this helps you! Greetings Georg Steffers
Ben <bench@silentmedia.com> writes:
> CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS
> 'DECLARE
> ...
> r record;
> ...
> RETURN next r;
I think you probably need "RETURN next r.setID", since r is a record not
an integer. The error message seems kind of unhelpful though :-(
regards, tom lane
Worked like a charm. Thanks! On Thu, 2003-04-17 at 07:07, Georg Steffers wrote: > Hi, > > i think its really you whos confused *g* > > Am Don, 2003-04-17 um 07.40 schrieb Ben: > <snip> > > The stored proc is: > > CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS > > 'DECLARE > > artists alias for $1; > > index integer := 1; > > total integer := 0; > > r record; > > q text := ''select setID from (select setID,count(*) as c from artistSet where''; > <snip> > > ok, the proc returns a setof integer. > > <snip> > > > > for r in execute q > > LOOP > > RETURN next r; > > END LOOP; > > RETURN; > > END; > <snip> > > but this returns a record....so what you need here is > > return next r.setID; > > Hope this helps you! > > Greetings > Georg Steffers > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org