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
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;