PostgresQL - define SEQUENCE for a SELECT query

Question

I have a VIEW for which I use a certain SELECT statement, where there is a CASE condition, which, based on some condition being TRUE or FALSE decides, whether to increment the counter or use the current value.

As far as I know, any view of a SELECT statement is generated every time it is referenced, so I would like to recreate the SEQUENCE every time that happens.

Example:

CREATE TABLE num(
n BIGINT PRIMARY KEY
);

INSERT INTO num(n) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

CREATE TEMP SEQUENCE seq START 1;
SELECT nextval('seq');

SELECT 
CASE WHEN n % 2 = 0 THEN nextval('seq')
     ELSE currval('seq')
END AS test,
n
FROM num;

I had to insert SELECT nextval('seq') before the SELECT statement, otherwise it yielded

ERROR:  currval of sequence "seq" is not yet defined in this session

And at the end of the day this code doesn't recreate the SEQUENCE each time, as it would only use the created one to generate new values.

This chunk of code works as expected, but smells like bad database design from my side.

Would any one of you guys know, how to create a new SEQUENCE object every time the SELECT is called? Or is there another way? I feel like there should be a clean and clear way of doing that, and I'm surely missing out on something!

Depending on how this is used, you would get an increment for every row in the view, not once per view. — Gordon Linoff
– Gordon Linoff, Commented Dec 23, 2020 at 15:34
@GordonLinoff I tried explaining better and provided a minimal working example. Please, check the update. — winwin
– winwin, Commented Dec 23, 2020 at 15:43

Gordon Linoff · Accepted Answer · 2020-12-23 16:04:12Z

2

For your example, you can set the value of the sequence in the SELECT:

SELECT (CASE WHEN n % 2 = 0 THEN nextval('seq')
             ELSE currval('seq')
        END) AS test,
       n
FROM num CROSS JOIN
     (SELECT setval('seq', 1)) s;

The FROM clause should be evaluated before the outer SELECT, so the outer SELECT should start at the specified value.

Here is a db<>fiddle.

I should note that this does suggest a problem with the database design. However, I could imagine a view containing:

FROM (SELECT NEXT_VALUE('view_cnt')) x CROSS JOIN
     . . .

To count the number of times the view has been called. That might be handy information.

answered Dec 23, 2020 at 16:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

winwin Over a year ago

doesn't cross join require too many operations? Because from the looks of it it maps every value of set A to every value of set B. How is it possible, since B (the sequence) is virtually infinite?

Gordon Linoff Over a year ago

@winwin . . . You are misunderstanding the code. The subquery gets executed once (because it is in the FROM clause). It returns one row. That row is then "cross joined" to the rest of the query. However, it doesn't do much except to make the just-calculated column available. But because it is not used, it really has no effect.

Collectives™ on Stack Overflow

PostgresQL - define SEQUENCE for a SELECT query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related