PostgreSQL function with select returning one value

Question

I have a table years (jan 1st of each ear). I want to create a function that takes each of those years and runs a query on another table to count the number of rows that belonging to that year. I tried to implement with pl/pgsql, using the code bellow, but I'm not getting it to work or finding any reference on the Internet.

CREATE or REPLACE FUNCTION func_test(in AAA date, OUT _result int) 
BEGIN
   SELECT SUM(st_length(geom)) as BBB
   FROM hist_line
   WHERE '2006-01-01' BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31');
   RETURN _result BBB;
END;
$$ LANGUAGE plpgsql;
CREATE or REPLACE FUNCTION func_test(date) 

select func_test('2009-01-01');

I'm not able to recover the content query inside the function as the output of the function.

So, do you use Oracle or Postgresql? – Guneli May 06 '14 at 18:09 — Guneli, May 06 '14 at 18:09
@ Guneli: i use Postgresql – LucasMation May 06 '14 at 18:42 — LucasMation, May 06 '14 at 18:42

score 14 · Answer 1 · answered May 07 '14 at 06:05

Your code has more than one issue, but main bug is wrongly using SELECT statement - what do you do has not sense. Embedded SQL in plpgsql has special clause "INTO targetvar":

CREATE OR REPLACE FUNCTION func_test(in AAA date, OUT _result int)
AS $$
BEGIN
   SELECT SUM(st_length(geom)) INTO _result
      FROM hist_line
     WHERE AAA BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31');
   RETURN;
END;
$$ LANGUAGE plpgsql;

These one line functions should not be implemented in plpgsql. Just use SQL.

CREATE OR REPLACE FUNCTION func_test(in AAA date)
RETURNS int AS $$
   SELECT SUM(st_length(geom)) 
      FROM hist_line
     WHERE $1 BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31');
$$ LANGUAGE sql;

Good source about PL/pgSQL is related chapter in PostgreSQL documentation. Second source can be my tutorial.

Erwin Brandstetter · Answer 2 · 2020-06-06T22:21:49.593

What @Pavel said. Plus, according to your description, you do not need a function at all.

I want to create a function that takes each of those years and runs a query on another table to count the number of rows that belonging to that year.

"Belonging to a year" seems to mean "overlapping time range". Use a simple query with a JOIN, a smart join condition and aggregation.

Assuming this table (missing in the question):

CREATE TABLE years (
  the_date date PRIMARY KEY
);

This query would do the job:

SELECT the_date, count(*) AS row_ct
FROM   years y
JOIN   other_table o ON (y.the_date, y.the_date + interval '1 year')
               OVERLAPS (o.valid_from, COALESCE(o.valid_to, 'infinity'))
GROUP  BY 1;

About the OVERLAPS operator:

Find overlapping date ranges in PostgreSQL

The manual about 'infinity'.

PostgreSQL function with select returning one value

2 Answers2