Re: function returns no results
От | Andreas Kretschmer |
---|---|
Тема | Re: function returns no results |
Дата | |
Msg-id | 20151205182354.GA10555@tux обсуждение исходный текст |
Ответ на | function returns no results (richard@xentu.com) |
Ответы |
Re: function returns no results
|
Список | pgsql-novice |
richard@xentu.com <richard@xentu.com> wrote: > Could anyone tell me what I'm doing wrong here? > I have a table, that does indeed contain data: > > SELECT last_name, first_name, email, department, salary from employees > where department='Engineering'; > > returns 4 rows. > I've also tried to define a function that should return a table: > > CREATE OR REPLACE FUNCTION get_employees_for_department(IN > the_department character varying) > RETURNS TABLE(last_name character varying, first_name character > varying, email character varying, department character varying, salary > numeric) AS > $BODY$ > begin > return query SELECT last_name, first_name, email, department, salary > from employees where department=the_department; > end > $BODY$ > LANGUAGE plpgsql; > > > However, when I try using that function: > > select * from get_employees_for_department('Engineering'); > > No rows are returned. have you got an error? similar example: test=*# select * from foo; key | val -------+------- key 1 | val 1 key 2 | val 2 (2 rows) test=*# create or replace function get_val(in in_key text) returns table(key text, val text) as $$begin return query selectkey, val from foo where key=in_key; end; $$language plpgsql; CREATE FUNCTION test=*# select * from get_val('key 1'); ERROR: column reference "key" is ambiguous LINE 1: select key, val from foo where key=in_key ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: select key, val from foo where key=in_key CONTEXT: PL/pgSQL function get_val(text) line 1 at RETURN QUERY Rewrite the funktion to: test=*# create or replace function get_val(in in_key text) returns table(key text, val text) as $$begin return query selectfoo.key, foo.val from foo where foo.key=in_key; end; $$language plpgsql; CREATE FUNCTION test=*# select * from get_val('key 1'); key | val -------+------- key 1 | val 1 (1 row) Maybe you are using an old version? New versions (since ???) raise an error, see above. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
В списке pgsql-novice по дате отправления: