CODEWITHSUNDEEP
sqldatabasepostgresqlsequence
winwin
winwin

Reputation: 1844

PostgresQL - define SEQUENCE for a SELECT query

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!

Upvotes: 0

Views: 1914

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

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.

Upvotes: 2

Related Questions