Re: function returns no results

On 2015-12-05 18:23, Andreas Kretschmer wrote:
> 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.


> 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


It works!

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
     employees.last_name,
     employees.first_name,
     employees.email,
     employees.department,
     employees.salary
   from
     employees
   where
     employees.department=the_department;
end
$BODY$
   LANGUAGE plpgsql;


Seems odd though. Had the function definition been ambiguous, I'd have
expected the function not to have been successfully created. I'm using
8.4.11

Thanks for your help.