Unexpected Return from Function
От | Anthony Bouvier |
---|---|
Тема | Unexpected Return from Function |
Дата | |
Msg-id | CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com обсуждение исходный текст |
Ответы |
Re: Unexpected Return from Function
|
Список | pgsql-sql |
I have a FUNCTION: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := responsible_list || '', ''my_record.login; END LOOP; RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; The employee table is such: id | login ------------- 1 | anthony 2 | mary ------------- I expect the SQL statement "SELECT get_responsible('1,2')" to return something like so: get_responsible --------------- anthony, mary --------------- But instead I receive: get_responsible --------------- --------------- If I get rid of the concatenation, like so: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := my_record.login; END LOOP;RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; I receive last result (for id = 2), like so: get_responsible --------------- mary --------------- The SELECT statement itself runs fine, so I know it is returning two records. Also, the reason I am passing a 'text' datatype to the function, is because I'd ultimately like to have the "WHERE id IN" statement to be dynamic, like so: CREATE FUNCTION get_responsible(text) RETURNS TEXT AS ' DECLARE responsible_list text; my_record RECORD; BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN ($1) LOOP responsible_list := my_record.login; END LOOP;RETURN responsible_list; END; ' LANGUAGE 'plpgsql'; If anyone can help me with this, I'd be much appreciative. I've been trying combination after combination of things to try and resolve this for the past 6 and a half hours. Thanks, Anthony "pulling his hair out" Bouvier
В списке pgsql-sql по дате отправления: