Alter Table in a Postgres function

Question

I am executing an alter table command and adding a new column with default value it is showing an error.

CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $$
BEGIN
EXECUTE format('ALTER TABLE viminfo ADD COLUMN vimtype character varying(64) NOT NULL DEFAULT (NA), ADD COLUMN vimname character varying(255) NOT NULL DEFAULT (NA)');
END
$$language plpgsql;

Error: ERROR: column "na" does not exist

What is wrong I am doing here.. Thanks

Why the execute? You can write the DDL statement directly in the function without the use of dynamic SQL. — user330315
– user330315, Commented May 11, 2017 at 14:30

Vao Tsun · Accepted Answer · 2017-05-11 12:41:42Z

1

if you don't quote the string it is understood as object, try this:

    CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $$
BEGIN
EXECUTE format('ALTER TABLE viminfo ADD COLUMN vimtype character varying(64) NOT NULL DEFAULT %L, ADD COLUMN vimname character varying(255) NOT NULL DEFAULT %L','NA','NA');
END
$$language plpgsql;

answered May 11, 2017 at 12:41

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Alter Table in a Postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related