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.....
