Simple postgres function

Question

I try to declare simple function in postgres but I cannot make it work. It looks like this:

CREATE OR REPLACE FUNCTION test2(rok int, id int)
  RETURNS int AS $$
BEGIN
select sum(data_zakonczenia-data_rozpoczecia) from historia where id_eksponatu = $2 AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01');
END;
$$ LANGUAGE 'plpgsql';

I get following error

ERROR:  query has no destination for result data
PODPOWIEDŹ:  If you want to discard the results of a SELECT, use PERFORM instead.
KONTEKST:  PL/pgSQL function "test2" line 2 at SQL statement

I am not sure how to make it work.

in PL/pgSQL you need to select into a variable then return that variable. Or you can change 'language' to 'sql' — Neil McGuigan
– Neil McGuigan, Commented Jun 7, 2018 at 16:32

user330315 · Accepted Answer · 2018-06-07 16:36:32Z

You want a SQL function:

CREATE OR REPLACE FUNCTION test2(rok int, id int)
  RETURNS int AS 
$$
   select sum(data_zakonczenia-data_rozpoczecia) 
   from historia where id_eksponatu = $2 
    AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') 
    AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01');
$$ LANGUAGE sql;

Or if you need PL/pgSQL for some reason, you need to use return query

CREATE OR REPLACE FUNCTION test2(rok int, id int)
  RETURNS int AS $$
BEGIN
  return query
    select sum(data_zakonczenia-data_rozpoczecia) 
    from historia where id_eksponatu = $2 
     AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') 
     AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01');
END;
$$ LANGUAGE plpgsql;

Or if you need to do something with the value before returning:

CREATE OR REPLACE FUNCTION test2(rok int, id int)
  RETURNS int AS $$
declare 
   l_sum integer;
BEGIN
  select sum(data_zakonczenia-data_rozpoczecia) 
      into l_sum
  from historia where id_eksponatu = $2 
   AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') 
   AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01');

   ....

  return l_sum;         
END;
$$ LANGUAGE plpgsql;

The language name is an identifier. Do not put it in single quotes.

SekstusEmpiryk · Accepted Answer · 2018-06-07 16:36:20Z

0

Ok, following hint in the comment I made this:

CREATE OR REPLACE FUNCTION test2(rok int, id int)
  RETURNS int AS $$
DECLARE
suma int;
BEGIN
select sum(data_zakonczenia-data_rozpoczecia) into suma from historia where id_eksponatu = $2 AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01');
return suma;
END;
$$ LANGUAGE 'plpgsql';

answered Jun 7, 2018 at 16:36

SekstusEmpiryk

1636 bronze badges

Comments

Thaw · Accepted Answer · 2018-06-07 20:41:52Z

0

RETURN (select sum(data_zakonczenia-data_rozpoczecia) from historia where id_eksponatu = $2 AND data_rozpoczecia>DATE(to_char($1,'9999') || '-01-01') AND data_zakonczenia<DATE(to_char($1+1,'9999') || '-01-01'));

Edit: You have use the "return" statement. I think another answer suggests you can use "return query" if you want to return a table instead of an integer.

edited Jun 7, 2018 at 20:41

answered Jun 7, 2018 at 16:39

Thaw

8619 silver badges19 bronze badges

2 Comments

Andrew_Lvov Over a year ago

Please, provide context, not just correct code. What's wrong with code provided by question author ? What is your improvement ?

Thaw Over a year ago

@Andrew_Lvov ok

Collectives™ on Stack Overflow

Simple postgres function

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related