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