samedi 25 juin 2016

Properly formatting an execute alter with variable and table name in postgres

I'm trying to execute on an alter table to assign a column to a sequence nextval for an auto-increment, but can't seem to figure out how to do this last part. The sequence is created fine, owners are all asigned, and table_a.id is the owner of table_a_id_seq. I've already created table_a_id_seq.

In a postgres sql function, how do I format this correctly.

I've tried:

EXECUTE format('ALTER TABLE ONLY %s ALTER COLUMN id SET DEFAULT nextval($1::regclass)', new_table_name) USING new_seq_name;

But it says that $1 is not pointing to new_table_seq_name. I've also tried:

EXECUTE format('ALTER TABLE ONLY %s ALTER COLUMN id SET DEFAULT nextval("%s"::regclass)', new_table_name, new_seq_name);

But it tells me the sequence doesn't exist which makes me wonder if it needs to be behind a transaction separated by this statement.

How can I successfully execute this alter on new_table_name? Thanks for the help!

Aucun commentaire:

Enregistrer un commentaire