Alter table add column default and execute the default for each row

Question

I am making a function that adds a id column to a given table, creates a sequence and fills the new columns value. The thing is that the column is created but now I need to fill it with nextval() of the created sequence (1,2,3,4,5...). I don't know how to specify that in the add column sentence.

CREATE OR REPLACE FUNCTION create_id(tabla character varying)
  RETURNS void AS
$BODY$ 
DECLARE

BEGIN

IF NOT EXISTS (SELECT information_schema.columns.column_name FROM information_schema.columns WHERE information_schema.columns.table_name=tabla AND information_schema.columns.column_name='id')
THEN
    EXECUTE 'ALTER TABLE '|| tabla ||' ADD COLUMN id numeric(8,0)';

    IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seq_id_'||tabla) 
    THEN
        EXECUTE 'CREATE SEQUENCE seq_id_'||tabla||' INCREMENT 1  MINVALUE 1  MAXVALUE 9223372036854775807  START 1 CACHE 1';
        EXECUTE 'GRANT ALL ON TABLE seq_id_'||tabla||' TO postgres';
        EXECUTE 'ALTER TABLE ONLY '||tabla||' ALTER COLUMN id SET DEFAULT nextval(''seq_id_'||tabla||'''::regclass)';
    END IF; 
END IF;

RETURN;
END;
$BODY$
  LANGUAGE plpgsql;

You need to update new `id` column, filling it with nextval? Sorted by what criteria? — keltar, Mar 28 '14 at 10:59
sorted by no criteria, the first record first value, second record second value, third record third value.. — Egidi, Mar 28 '14 at 11:07
There is no 'first' and 'second' words if there is no sorting criteria... Unordered select may return you values in any order it likes more. — keltar, Mar 28 '14 at 11:10
If you don't care about order at all, just add `execute 'update ' || tabla || ' set id = nextval(''seq_id_'||tabla||'')';` — keltar, Mar 28 '14 at 11:14

score 2 · Accepted Answer · edited May 23 '17 at 11:50

Your function suffers from a number of series problems. Use this instead:

CREATE OR REPLACE FUNCTION f_create_id(_tbl text)
  RETURNS void AS
$func$ 
DECLARE
   _seq text := _tbl || '_id_seq';
BEGIN

IF EXISTS (
   SELECT 1 FROM pg_namespace n
   JOIN   pg_class     c ON c.relnamespace = n.oid
   JOIN   pg_attribute a ON a.attrelid = c.oid 
   WHERE  n.nspname = current_schema()  -- default to current schema
   AND    c.relname = _tbl
   AND    a.attname = 'id'
   AND    NOT a.attisdropped)
THEN
   RAISE EXCEPTION 'Column already exists!'; RETURN;
END IF;

IF EXISTS (
   SELECT 1 FROM pg_namespace n
   JOIN   pg_class     c ON c.relnamespace = n.oid
   WHERE  n.nspname = current_schema()  -- default to current schema
   AND    c.relname = _seq)
THEN
   RAISE EXCEPTION 'Sequence already exists!'; RETURN;
END IF; 

EXECUTE format('CREATE SEQUENCE %I.%I', current_schema(), _seq;    
EXECUTE format($$ALTER TABLE %I.%I ADD COLUMN id numeric(8,0)
               DEFAULT nextval('%I'::regclass)$$  -- one statement!
               , current_schema(), _tbl, _seq);

END
$func$  LANGUAGE plpgsql;

Major points

If you set the column default in the same ALTER TABLE statement, values are inserted automatically. Be aware that this makes a big difference in performance for big tables, since every row has to be updated, while adding a NULL column only needs a tiny change to the system catalog.
You must define the schema to create objects in. If you want to default to the current schema, you still have to consider this in your queries to catalog (or information schema) tables. Table names are only unique in combination with the schema name.
I use the session information functions current_schema() to find out the current schema.
You must safeguard against SQL injection when using dynamic SQL with user input. Details:
Table name as a PostgreSQL function parameter
If the sequence already exists, do not use it! You might interfere wit existing objects.
Normally, you do not need EXECUTE GRANT ALL ON TABLE ... TO postgres. If postgres is a superuser (default) the role has all rights anyway. You might want to make postgres the owner. That would make a difference.
I am using the system catalog in both queries, while you use the information schema in one of them. I am generally not a fan of the information schema.Its bloated views are slow. The presented information adheres to a cross-database standard, but what's that good for when writing plpgsql functions, which are 100% not portable anyway?

Superior alternative

I would suggest not to use the column name id, which is an SQL anti-pattern. Use a proper descriptive name instead, like tablename || '_id'.
What's the point of using numeric(8,0)? If you don't want fractional digits, why not use integer? Simpler, smaller, faster.

Given that, you are much better off with a serial type, making everything much simpler:

CREATE OR REPLACE FUNCTION f_create_id(_tbl text)
  RETURNS void AS
$func$ 
BEGIN

IF EXISTS (
   SELECT 1 FROM pg_namespace n
   JOIN   pg_class     c ON c.relnamespace = n.oid
   JOIN   pg_attribute a ON a.attrelid = c.oid 
   WHERE  n.nspname = current_schema()  -- default to current schema
   AND    c.relname = _tbl
   AND    a.attname = _tbl || '_id'     -- proper column name
   AND    NOT a.attisdropped)
THEN
   RAISE EXCEPTION 'Column already exists!';
ELSE
   EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I serial'
                 , current_schema(), _tbl, _tbl || '_id');
END IF;

END
$func$  LANGUAGE plpgsql;

Alter table add column default and execute the default for each row

1 Answers1

Major points

Superior alternative