Re: function returns no results

richard(at)xentu(dot)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 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.

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 character varying, 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;