Pl/Pgsql function troubles: FOLLOW UP
От | Jeff Post |
---|---|
Тема | Pl/Pgsql function troubles: FOLLOW UP |
Дата | |
Msg-id | 1018994294.3cbc9e7668075@www.panthermail.uwm.edu обсуждение исходный текст |
Ответ на | cannot restore db ("P. Jourdan" <pippo@videotron.ca>) |
Ответы |
Re: Pl/Pgsql function troubles: FOLLOW UP
Re: Pl/Pgsql function troubles: FOLLOW UP |
Список | pgsql-novice |
here is Code that I got working but I still have some problems with: (I am asking for help, so if you wish to skip to my question read the last sentance) CREATE or replace FUNCTION list_of_membership(integer,CHAR) RETURNS TEXT AS ' DECLARE membership_rec record; membership text := NULL; count integer := 0; sqlstr1 text := ''select name from org_details where person_id = ''; sqlstr2 text := '' and type = ''; sqlstr3 text := '' order by name;''; BEGIN FOR membership_rec IN EXECUTE sqlstr1 || $1 || sqlstr2 || $2 || sqlstr3 LOOP count := count + 1; IF count = 1 THEN membership := membership_rec.name; ELSE membership := membership || '', '' || membership_rec.name; END IF; END LOOP; RETURN membership; END; ' LANGUAGE 'plpgsql'; the problems are such: after the above is "compiled" and I try to do a select from it: Attempt number 1: =# SELECT list_of_membership(1,department); ERROR: Attribute 'department' not found This is my ideal methodfor calling this function. As you can see it has a problem with the second variable pass. The problem (after much head scratching) is determined to be that it thinks I am trying to pass it a defined type. when I am supposed to be passing it a string. Attempt number 2: =# SELECT list_of_membership(1,'department'); NOTICE: Error occurred while executing PL/pgSQL function list_of_membership NOTICE: line 10 at for over execute statement ERROR: Attribute 'department' not found Well this is esentually the same problem as above. But now in the internal SELECT sql. Attempt number 3: =# SELECT list_of_membership(1,'\'department\''); list_of_membership --------------------- Madison, Technology (1 row) YEA!!!! But MAN is that an UGLY call (especialy since I will be building this into perl scripts.) the '\' and \'' are going to cause me some trouble. Does anyone have any suggestions as to how I can make this look more like attempt number 1 or 2 ? THANKS! Jeff Post
В списке pgsql-novice по дате отправления: