CODEWITHSUNDEEP
postgresql
SekstusEmpiryk
SekstusEmpiryk

Reputation: 143

Simple postgres function

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.

Upvotes: 0

Views: 81

Answers (3)

Thaw
Thaw

Reputation: 861

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.

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 2

SekstusEmpiryk
SekstusEmpiryk

Reputation: 143

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

Upvotes: 0

Related Questions