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 по дате отправления: