problem with pl/pgsql

From: Ben <bench(at)silentmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: problem with pl/pgsql
Date: 2003-04-17 05:40:33
Message-ID: Pine.LNX.4.44.0304162236140.3894-100000@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-04-17 06:06:57 Re: "ERROR: Argument of WHERE must not be a set function"?
Previous Message Drew Wilson 2003-04-17 04:38:57 Re: "ERROR: Argument of WHERE must not be a set function"?