Re: Any Good Way To Do Sync DB's? - Mailing list pgsql-general

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