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°
