How to use a range of values in a PostgreSQL function and to loop with a distinct interval

Question

I have a very simple example for the summation of two numbers.

CREATE OR REPLACE FUNCTION add(a integer, b integer) RETURNS integer AS
$$
  SELECT $1+$2;
$$ LANGUAGE 'sql';

My question is how to define a range b 10:20, whose values will counted up by one until the end of the range (20) is reached.

The result hast to be like this

res = a + b
res = a + 10
res = a + 11
res = a + 12
res = a + 13

When I retrieve the function add with:

SELECT add(1);

there should be the results: 11,12,13,...,21.

I didn't use loops like FOR EACH before (especially in the LANGUAGE sql).

Is it better to write those functions in plpgsql?

Erwin Brandstetter · Accepted Answer · 2013-12-16 17:59:46Z

2

I didn't use loops like FOR EACH before (especially in the LANGUAGE sql).

There are no loops in SQL. (The only exception being RECURSIVE CTEs.) Functions with LANGUAGE sql (no quotes!), consist of SQL statements exclusively. If you need procedural elements like loops, you need to switch to PL/pgSQL (or any other procedural language) where looping is easy enough.

Of course, the simple example you presented would best be solved with generate_series(), as other answers already pointed out.

A word of caution: best use generate_series() in the FROM list. Using it in the SELECT list is allowed but frowned upon because non-standard. SRF (set returning functions) might be confined to the FROM list in future releases.

answered Dec 16, 2013 at 17:59

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2013-12-16 15:29:01Z

2

I think you are looking for the function generate_series(), documented here.

So,

select generate_series(1, 3)

Will return three rows:

1
2
3

You can use these numbers in arithmetic expressions.

answered Dec 16, 2013 at 15:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Paul Richter · Accepted Answer · 2013-12-16 17:45:57Z

0

select add(1, b)
from generate_series(10, 20) b

edited Dec 16, 2013 at 17:45

Paul Richter

11.1k10 gold badges56 silver badges89 bronze badges

answered Dec 16, 2013 at 17:29

marvinorez

6035 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to use a range of values in a PostgreSQL function and to loop with a distinct interval

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related