CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
Oto Shavadze
Oto Shavadze

Reputation: 42793

Generate sequence new values in dynamic sql

create sequence seq;

create table test(
    col int
);


do $$
begin
    EXECUTE 'INSERT INTO test
    (col)
    select '||nextval('seq')||' from generate_series(1,5)' ;
end;
$$ language plpgsql

this results same values for all 5 rows, it seems nextval('seq') executed only once.

Question: How to generate sequence new values in dynamic sql as it works in normal SQL query?

Upvotes: 1

Views: 515

Answers (1)

piezol
piezol

Reputation: 963

You're appending netval''s result to the string (which is your dynamic query).

So it's exactly the same as if you were doing this:

(...)
    EXECUTE 'insert into test 
    (col) 
    select 91533 from generate_series(1,5)';
(...)

Instead, you must make fetching number from sequence a part of your dynamic query.

(...)
    EXECUTE 'INSERT INTO test
    (col)
    select nextval(''seq'') from generate_series(1,5)';
(...)

(I do not know postgres but this is more of a general dbms question. It would be the same in Oracle, but with different syntax).

Upvotes: 1

Related Questions