Here's another way. I use a function that allows you
to specify a set # and the schema name to generate the 'SET ADD TABLE'
commands for.
eg: SELECT public.slony_generate_set_adds(3, 'my_schema');
will generate statements for all tables in my_schema
and prefix them with a set # of 3.
You can adjust it to do a similar gen for sequences.
=======================================================================
CREATE OR REPLACE FUNCTION public.slony_generate_set_adds(int4, text)
RETURNS void AS
$BODY$
DECLARE
p_set_id ALIAS FOR $1;
p_schema ALIAS FOR $2;
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 := 2;
v_ctr := 1;
SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0')
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 = 'r'::"char"
AND n.nspname = p_schema
ORDER BY c.oid
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 || '\') ;' INTO v_msg;
RAISE WARNING '%', v_msg;
v_ctr := v_ctr +1;
SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0')
INTO v_seq;
END LOOP;
v_prefix_digits := 3;
v_ctr := 1;
SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0')
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 n.nspname = p_schema
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 WARNING '%', v_msg;
v_ctr := v_ctr +1;
SELECT p_set_id::text || LPAD(v_ctr::text, v_prefix_digits, '0')
INTO v_seq;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
============================================================
Melvin Davidson
Database Developer
Computer & Communication Technologies, Inc.
6 Inverness Court East, Suite 220
Englewood, CO 80112
BEGIN:VCARD
VERSION:2.1
N:Davidson;Melvin
FN:Melvin Davidson
ORG:CCT
TEL;WORK;VOICE:303-708-9228x305
ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;6 Inverness Ct East=0D=0ASuite 220;Englewood;CO;80112;United States
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:6 Inverness Ct East=0D=0ASuite 220=0D=0AEnglewood, CO 80112=0D=0AUnited Stat=
es
EMAIL;PREF;INTERNET:[EMAIL PROTECTED]
REV:20060518T221645Z
END:VCARD
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
