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 <[email protected]>
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 ([email protected])
> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
