Re: function returns no results

richard(at)xentu(dot)com <richard(at)xentu(dot)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 select key, 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 select foo.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°