Обсуждение: function returns no results

Поиск
Список
Период
Сортировка

function returns no results

От
richard@xentu.com
Дата:
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.

Regards
Richard

Re: function returns no results

От
Andreas Kretschmer
Дата:
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°

Re: function returns no results

От
richard@xentu.com
Дата:
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.

Re: function returns no results

От
Thomas Kellerer
Дата:
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;

Re: function returns no results

От
Andreas Kretschmer
Дата:
richard@xentu.com <richard@xentu.com> wrote:

> It works!

Great!

>
> 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


8.4 isn't supported anymore, consider upgrade now!


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°

Re: function returns no results

От
Andreas Kretschmer
Дата:
Thomas Kellerer <spam_eater@gmx.net> wrote:

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

absolutely right, but that wasn't the point here ;-)

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°