Skip to content

Instantly share code, notes, and snippets.

@red-led

red-led/copyTableIndexes.sql

Last active August 29, 2015 14:18
Show Gist options
  • Save red-led/e5112ac17af91cd7d87d to your computer and use it in GitHub Desktop.
Save red-led/e5112ac17af91cd7d87d to your computer and use it in GitHub Desktop.
Download ZIP
Postgres function for table indexes and privileges copying
Raw
copyTableIndexes.sql
CREATE OR REPLACE FUNCTION
copyTableIndexes(fromSchema varchar, fromTable varchar, toSchema varchar, toTable varchar)
RETURNS void AS $$
DECLARE
oneIndex record;
q text;
BEGIN
IF
NOT EXISTS(
SELECT *
FROM information_schema.tables
WHERE table_schema = fromSchema AND table_name = fromTable
)
THEN
RAISE EXCEPTION 'Table % does not exists', '"' || fromSchema || '"."' || fromTable || '"';
END IF;
IF
NOT EXISTS(
SELECT *
FROM information_schema.tables
WHERE table_schema = toSchema AND table_name = toTable
)
THEN
RAISE EXCEPTION 'Table % does not exists', '"' || toSchema || '"."' || toTable || '"';
END IF;
FOR oneIndex IN
SELECT
n.nspname as ischema,
c.relname as itable,
i.relname as iname,
pg_get_indexdef(i.oid) AS idef,
regexp_replace(pg_get_indexdef(i.oid),
'"' || i.relname || '" ON "' || n.nspname || '"."' || c.relname || '"',
'ON "' || toSchema || '"."' || toTable || '"') as idef2
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE
c.relkind = 'r' AND
i.relkind = 'i' and
x.indisprimary = false and
n.nspname = fromSchema and c.relname = fromTable and
regexp_replace(pg_get_indexdef(i.oid), 'CREATE INDEX "' || i.relname || '" ON "' || n.nspname || '"."' || c.relname || '" ', '') not in (
SELECT
regexp_replace(pg_get_indexdef(i.oid), 'CREATE INDEX "' || i.relname || '" ON "' || n.nspname || '"."' || c.relname || '" ', '')
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE
c.relkind = 'r' AND
i.relkind = 'i' and
x.indisprimary = false and
n.nspname = toSchema and c.relname = toTable
)
LOOP
q := oneIndex.idef2;
-- RAISE NOTICE '%', q;
EXECUTE q;
END LOOP;
END
$$ LANGUAGE plpgsql;
Raw
copyTablePrivileges.sql
CREATE OR REPLACE FUNCTION
copyTablePrivileges(fromSchema varchar, fromTable varchar, toSchema varchar, toTable varchar)
RETURNS void AS $$
DECLARE
oneUser record;
q text;
BEGIN
IF
NOT EXISTS(
SELECT *
FROM information_schema.tables
WHERE table_schema = fromSchema AND table_name = fromTable
)
THEN
RAISE EXCEPTION 'Table % does not exists', '"' || fromSchema || '"."' || fromTable || '"';
END IF;
IF
NOT EXISTS(
SELECT *
FROM information_schema.tables
WHERE table_schema = toSchema AND table_name = toTable
)
THEN
RAISE EXCEPTION 'Table % does not exists', '"' || toSchema || '"."' || toTable || '"';
END IF;
FOR oneUser IN
SELECT
grantee,
string_agg(privilege_type, ', ') as privilege_list
FROM
information_schema.role_table_grants
WHERE
table_schema = fromSchema and table_name = fromTable
GROUP BY
grantee
LOOP
q := 'GRANT ' || oneUser.privilege_list || ' ON "' || toSchema || '"."' || toTable || '" TO "' || oneUser.grantee || '";';
-- raise notice '%', q;
EXECUTE q;
END LOOP;
END
$$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment