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;

Answer 1

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;