CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user2594191
user2594191

Reputation: 17

Alter Table in a Postgres function

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

Upvotes: 1

Views: 3935

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

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;

Upvotes: 1

Related Questions