PostgreSQL Squence Not Working When Updating Column Details

Question

I have created a function to update a column in a postgresSQL table using Sequence nextval() function.Function body is as follows

BEGIN
    EXECUTE 'CREATE SEQUENCE '|| sequence_name || ' START 1';
    EXECUTE 'UPDATE ' ||selected_table_name|| ' SET record_id = '||nextval(sequence_name);
    RETURN 'SUCCESS';
END;

But when I call function as follows

SELECT staging.update_record_id('staging.test_table','staging.sq_test_table');

Its update my relevant column with 1 for all the records.But when I just use the following command in the console directly it update the all the values with increments.The console code as follows.

update staging.test_table set record_id = nextval('staging.sq_test_table');

Is anyone can give a solution for this, would be much grateful

score 1 · Accepted Answer · answered Jan 29 '20 at 11:37

1

I found a solution for the question.The function body should change as follows

BEGIN
    EXECUTE 'CREATE SEQUENCE '|| sequence_name || ' START 1';
    EXECUTE 'UPDATE ' ||selected_table_name|| ' SET record_id = nextval('''||sequence_name||''')';
    RETURN 'SUCCESS';
END;

answered Jan 29 '20 at 11:37

Susampath

706
10
13

2

In your question you concatenate a string with the current value of the sequence (1). That is why all values are 1. In your answer you let the engine call nextval for each row. – some Jan 29 '20 at 11:48
@some Thanks for your explaination – Susampath Jan 29 '20 at 17:11

PostgreSQL Squence Not Working When Updating Column Details

1 Answers1