Some useful plpgsl - Mailing list pgsql-general

FWIW, and your improvements appreciated:


1) To change the schema associated with given tables.

CREATE FUNCTION public.alter_object_namespace(name, name) RETURNS text AS '
DECLARE
  ls_schema ALIAS FOR $1;
  ls_relation ALIAS FOR $2;
  li_schema integer;
  li_relation integer;
BEGIN
   SELECT INTO li_schema pg_namespace.oid FROM pg_catalog.pg_namespace
WHERE nspname = ls_schema;
   SELECT INTO li_relation pg_class.oid FROM pg_class WHERE
pg_class.relname = ls_relation;
   RETURN ''UPDATE pg_class set relnamespace = '' || li_schema || ''
WHERE pg_class.oid = '' || li_relation || '';'';
END;'  LANGUAGE 'plpgsql' VOLATILE;


Then a query like

SELECT alter_object_namespace('paid', tablename)
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'

produces output which can be run to change the schema in which given
tables live (note, the new schema name, 'paid' in this case, must already
be defined):

UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 60030;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 60115;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59698;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59703;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59723;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59718;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59754;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59713;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 59708;
UPDATE pg_class set relnamespace = 59633 WHERE pg_class.oid = 60135;



2) To grant privileges on all or some set of tables or views.

CREATE FUNCTION public.grant_table_privileges(name, name, name) RETURNS
text AS '
DECLARE
  ls_schema ALIAS FOR $1;
  ls_table ALIAS FOR $2;
  ls_user ALIAS FOR $3;
BEGIN
   RETURN ''GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE '' || ls_schema
|| ''.'' || ls_table || '' TO GROUP ''  || ls_user || '';'';
END;'  LANGUAGE 'plpgsql' VOLATILE;


Then a query like

SELECT grant_table_privileges(pg_tables.schemaname, pg_tables.tablename,
'paid_acct') AS grant_table_privileges FROM pg_tables WHERE
((pg_tables.schemaname = 'paid') OR (pg_tables.schemaname = 'public'));


produces output like

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE paid.user_options TO GROUP
paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE paid.account_type TO GROUP
paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE paid.transaction_type TO
GROUP paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE paid.depreciation_method TO
GROUP paid_acct;


etc., which can be used to quickly change table privileges. Similarly for
views the corresponding query would be

SELECT grant_table_privileges(pg_views.schemaname, pg_views.viewname,
'paid_acct')
FROM pg_views
WHERE ((pg_views.schemaname = 'paid') OR (pg_views.schemaname = 'public'));

producing output like

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE
public.application_version_v TO GROUP paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.group_members TO
GROUP paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.stored_procedures TO
GROUP paid_acct;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE paid.customer_v TO GROUP
paid_acct;

etc.

3) Table dependencies.

A view defined as

CREATE VIEW public.table_precedence AS SELECT b.relname AS
primary_key_table, a.relname AS foreign_key_table FROM pg_trigger,
pg_class a, pg_class b WHERE (((pg_trigger.tgtype = 21) AND
(pg_trigger.tgrelid = a.oid)) AND (pg_trigger.tgconstrrelid = b.oid))
ORDER BY b.relname;

comes in useful to get an overall look at what tables depend on which
others. Then a query like

SELECT primary_key_table || ', ' || foreign_key_table FROM
public.table_precedence


then produces a result set such as


billing_type, project
city, city_postal_code
country, province
courtesy_title, person
customer, project
customer, customer_invoice
customer_invoice, project_labor_actual
customer_invoice, project_expense_actual
dependent_relationship, dependent
employee, employee_leave_journal
employee, project
employee, employee_wage_journal
employee, position

etc., which you could use as a set of pairwise precedence relations to
filter through a topological sort and thereby determine the order in
which tables should be created so that all dependent tables have their
dependencies satisfied.

~Berend Tober