Re: function returns no results

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°