PostgreSQL: Copying scoped tables

copy_row_from_table.sql

-- This function inserts a copy of an existing row (with primary key equal to `_id`) into a table called `_tbl`.
-- Returns a `row_id` which is a primary key of the new row.

CREATE OR REPLACE FUNCTION copy_row_from_table(_tbl regclass, _id uuid, OUT row_id uuid) AS
$func$
BEGIN
  EXECUTE (
    SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1 RETURNING id',
                  _tbl, string_agg(quote_ident(attname), ', '))
    FROM   pg_attribute
    WHERE  attrelid = _tbl
    AND    NOT attisdropped  -- no dropped (dead) columns
    AND    attnum > 0        -- no system columns
    AND    attname <> 'id'   -- exclude id column
    )
   INTO row_id USING _id;
END
$func$
LANGUAGE plpgsql;

copy_scoped_rows_from_table.sql

-- This function creates copies of all rows from a table called `_tbl`,
-- whose value in a column named `_scope_column_name` is equal to `_scope_column_value`.
-- Returns a hstore containing `old_id, new_id` pairs, where `old_id` is a primary key of
-- a row being copied and `new_id` is a primary key of a freshly created row.

CREATE OR REPLACE FUNCTION copy_scoped_rows_from_table(_tbl regclass, _scope_column_name text, _scope_column_value uuid)
RETURNS hstore AS
$func$
DECLARE
  _id_pairs hstore;
  _old_id uuid;
  _new_id uuid;
BEGIN
  FOR _old_id IN EXECUTE format('SELECT id FROM %1$s WHERE %2$s = ''%3$s''', _tbl, _scope_column_name, _scope_column_value)
  LOOP
    EXECUTE format('SELECT copy_row_from_table(''%1$s'', ''%2$s'')', _tbl, _old_id)
    INTO _new_id;
    SELECT COALESCE(_id_pairs, hstore('')) || hstore(_old_id::text, _new_id::text) INTO _id_pairs;
  END LOOP;
  RETURN _id_pairs;
END
$func$
LANGUAGE plpgsql;

copy_tables.sql

-- This function creates copies of all rows from tables contained in `_tbls` array,
-- whose value in a column called `_scope_column_name` is equal to `_scope_column_value`,
-- and replaces this value with `_scope_column_new_value`.

CREATE OR REPLACE FUNCTION copy_tables(_tbls regclass[], _scope_column_name text, _scope_column_value uuid, _scope_column_new_value uuid)
RETURNS void AS
$func$
DECLARE
  _id_pairs hstore;
  _table_id_pairs hstore;
  _row record;
  _hs_row record;
BEGIN
  FOR I IN array_lower(_tbls, 1)..array_upper(_tbls, 1)
  LOOP
    EXECUTE format('SELECT copy_scoped_rows_from_table(''%1$s'', ''%2$s'', ''%3$s'')', _tbls[I], _scope_column_name, _scope_column_value)
    INTO _table_id_pairs;
    SELECT COALESCE(_id_pairs, hstore('')) || COALESCE(_table_id_pairs, hstore('')) INTO _id_pairs;
  END LOOP;

  FOR I IN array_lower(_tbls, 1)..array_upper(_tbls, 1)
  LOOP
    FOR _row IN EXECUTE format('SELECT * FROM %1$s WHERE id = ANY(''%2$s''::uuid[])', _tbls[I], avals(_id_pairs))
    LOOP
      EXECUTE format('UPDATE %1$s SET %2$s = ''%3$s'' WHERE id = ''%4$s''',
                      _tbls[I], _scope_column_name, _scope_column_new_value, _row.id);
      FOR _hs_row IN SELECT kv."key", kv."value" FROM each(hstore(_row)) kv
      LOOP
        IF _hs_row."value" = ANY(akeys(_id_pairs)) THEN
          EXECUTE format('UPDATE %1$s SET %2$s = ''%3$s'' WHERE id = ''%4$s''',
                          _tbls[I], _hs_row."key", _id_pairs -> _hs_row."value", _row.id);
        END IF;
      END LOOP;
    END LOOP;
  END LOOP;
END
$func$
LANGUAGE plpgsql;