On 12 Oct 2001, Doug McNaught wrote:
> Probably the best thing to do is to export the data from Progress in a
> format that the PostgreSQL COPY command can read. See the docs for
> details.
Hi,
I wrote a quick and dirty function/trigger to sync two DBs - one
local and the other on the web. The method is quite simple. Any insert or
update fires a trigger that "serialises" the entries and stores it in a
log with the table name, the primary key and the timestamp. When an entry
is deleted, the same happens except that the serialised column contains a
null. So when I sync, I just need to upload the changes and not the entire
dump. I think this is a good opportunity to get some advice feedback on
the code, so here it is:
--------------------------------------------------------------------------------------------
drop function setuptriggers();
create function setuptriggers() returns int as '
declare
fb text;
tb text;
tresult record;
cresult record;
pkeyname name;
begin
for tresult in select * from pg_class
where relkind = ''r''
and relname !~ ''^pg_''
and relname !~ ''^Inv''
and relname !~ ''^pga_''
order by relname
loop
select into pkeyname c.attname from pg_class a, pg_index b,
pg_attribute c
where a.relname = tresult.relname and
a.oid = b.indrelid and
a.oid = c.attrelid and
b.indkey[0] = c.attnum and
b.indisprimary=''t'';
if pkeyname is not null and tresult.relname != ''logtable''
then
fb := ''
create function logchange_'' ||
quote_ident(tresult.relname) || ''() returns opaque as ''''
declare
serialized text;
updatetime timestamp;
separator text;
begin
updatetime := ''''''''now'''''''';
separator := chr(178);
serialized := '''''''''''''''';
'';
for cresult in select * from pg_class a, pg_attribute b
where a.relname = tresult.relname and
a.oid = b.attrelid and
b.attnum > 0
order by b.attnum
loop
fb := fb || '' if NEW.'' ||
quote_ident(cresult.attname) || '' is not null then
serialized := serialized || separator ||
'''''''''' || quote_ident(cresult.attname) || ''='''''''' || NEW.'' ||
quote_ident(cresult.attname) || '';
end if;
'';
end loop;
fb := fb || '' insert into logtable values (NEW.''||
quote_ident(pkeyname) || '', '''''''''' || quote_ident(tresult.relname) || '''''''''',
serialized, updatetime);
return new;
end;''''
language ''''plpgsql'''';'';
execute fb;
tb := ''create trigger fireon_'' ||
quote_ident(tresult.relname) || '' before insert or update on '' ||
quote_ident(tresult.relname) || ''
for each row execute procedure logchange_'' ||
quote_ident(tresult.relname) || ''();'';
execute tb;
end if;
end loop;
return 1;
end;'
language 'plpgsql';
-------------------------------------------------------------------------------------------------
I hope this is usefule
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
