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

2014-05-06 18:09:43 +00:00
Commented May 6, 2014 at 18:09 — Guneli
– Guneli, Commented May 6, 2014 at 18:09
@ Guneli: i use Postgresql

LucasMation
– LucasMation

2014-05-06 18:42:30 +00:00
Commented May 6, 2014 at 18:42 — LucasMation
– LucasMation, Commented May 6, 2014 at 18:42

Pavel Stehule · Accepted Answer · 2014-05-07 06:05:32Z

16

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.

answered May 7, 2014 at 6:05

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2020-06-06 22:21:49Z

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

Collectives™ on Stack Overflow

PostgreSQL function with select returning one value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related