Re: function returns no results - Mailing list pgsql-novice

richard@xentu.com schrieb am 05.12.2015 um 20:07:

>> 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
charactervarying, 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
successfullycreated. 

A plain SQL function is enough for this (and more efficient):

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
charactervarying, salary numeric) 
AS
$BODY$
    SELECT
      employees.last_name,
      employees.first_name,
      employees.email,
      employees.department,
      employees.salary
    from
      employees
    where
      employees.department=the_department;
$BODY$
LANGUAGE sql;