problem with pl/pgsql
От | Ben |
---|---|
Тема | problem with pl/pgsql |
Дата | |
Msg-id | Pine.LNX.4.44.0304162236140.3894-100000@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: problem with pl/pgsql
Re: problem with pl/pgsql Re: problem with pl/pgsql |
Список | 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.....
В списке pgsql-general по дате отправления: