Re: table dependencies

[Melvin Davidson <melvin6925@xxxxxxxxx>]

All,
fwiw,  I once wrote a plpgsql function to assist in generating slony set adds.

It grabs all the tables in pg_class and sorts them by foreign key count.

You can pull the main query logic from it and modify to suit your needs.

I've attached for your convenience.

On Mon, Sep 7, 2015 at 6:27 AM, Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> wrote:

On 07/09/15 19:44, Raymond O'Donnell wrote:

On 06/09/2015 22:59, FarjadFarid(ChkNet) wrote:

No worries.

I found a way.

Would you share it, for the archives?

Ray.

I think I can do it relatively simply, in a reasonable general fashion.

if it is of real interest let me know, and I'll see if I can find time to try and implement  it.


Cheers,
Gavin

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

-- Function: slony_generate_set_adds(integer, text, integer)

-- DROP FUNCTION slony_generate_set_adds(integer, text, integer);

CREATE OR REPLACE FUNCTION slony_generate_set_adds(integer, text, integer)
  RETURNS void AS
$BODY$

DECLARE

p_set_id	ALIAS FOR $1;
p_schema	ALIAS FOR $2;
p_start_seq 	ALIAS FOR $3;

v_record	record;
v_schema	TEXT;
v_relname	TEXT;
v_seq		TEXT;
v_prefix_digits	INT4;
v_ctr		INT4;

v_msg		TEXT;

BEGIN

v_prefix_digits := 3;
v_ctr 		:= p_start_seq;

CREATE TEMP TABLE t_slony_set_add
( slonik_command TEXT )
ON COMMIT DROP;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

-- 2008-06-12
-- Sorting by OID replaced by sort by FK count

FOR v_record IN SELECT n.nspname, t.relname , 0 as count
                  FROM pg_class t
                  JOIN pg_namespace n ON n.oid = t.relnamespace
                 WHERE relkind = 'r'
                   AND relname NOT LIKE 'pg_%'
                   AND relname NOT LIKE 'sql_%'
                   AND n.nspname = p_schema
                   AND t.OID NOT IN 
                            (SELECT conrelid
                               FROM pg_constraint
                              WHERE contype = 'f'
                                AND contype <> 'p'
                                AND contype <> 'c')
                UNION
               SELECT n.nspname, t.relname as table, count(c.conname) as count
                 FROM pg_class t
                 JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
                 JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE relkind = 'r'
                  AND t.relname NOT LIKE 'pg_%'
                  AND t.relname NOT LIKE 'sql_%'
                  AND n.nspname != 'rollback'
                  AND CASE WHEN p_schema IS NOT NULL 
			THEN n.nspname = p_schema
			ELSE TRUE
			END
            GROUP BY n.nspname, t.relname
            ORDER BY 3, 2

LOOP

SELECT 'SET ADD TABLE (SET ID=' || p_set_id || ', ORIGIN=1, ID=' 
	|| v_seq || ', FULLY QUALIFIED NAME=' || ''''
	|| v_record.nspname || '.' || v_record.relname || '''' || ', comment=' || '''' 
	|| v_record.relname || ' fks->'|| v_record.count::text || ''');' INTO v_msg;

INSERT INTO t_slony_set_add 
( slonik_command )
VALUES
( v_msg);

RAISE NOTICE '%', v_msg;

	v_ctr := v_ctr +1;
	--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
	SELECT v_ctr INTO v_seq;

END LOOP;

v_prefix_digits := v_prefix_digits + 1;
--v_ctr 		:= 1;
v_ctr 		:= p_start_seq;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

FOR v_record IN SELECT n.nspname, c.relname 
	          FROM pg_class c
	     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
	     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
	         WHERE c.relkind = 'S'::"char"
		   AND CASE WHEN p_schema IS NOT NULL 
			THEN n.nspname = p_schema
			ELSE TRUE
			END
	      ORDER BY c.oid

LOOP

SELECT 'SET ADD SEQUENCE (SET ID=' || p_set_id || ', ORIGIN=1, ID='
	|| v_seq || ', FULLY QUALIFIED NAME='
	|| '''' || v_record.nspname || '.'|| v_record.relname || '''' || ', comment='
	|| '''' || v_record.relname || ''');' INTO v_msg;

RAISE NOTICE '%', v_msg;
INSERT INTO t_slony_set_add 
( slonik_command )
VALUES
( v_msg);

v_ctr := v_ctr +1;

--SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0') INTO v_seq;
SELECT v_ctr INTO v_seq;

END LOOP;

COPY t_slony_set_add 
--TO '/tmp/slony_set_adds.txt';
TO 'F:\temp\slony_set_adds.txt';

RETURN;
-- SAMPLE CALL
-- SELECT slony_generate_set_adds(1, 'public', 1)
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION slony_generate_set_adds(integer, text)
  OWNER TO postgres;

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general