Re: Duplicating a table with a trigger

On Tue, Sep 3, 2013 at 1:11 PM, Wayne Oliver <wayn0(dot)ml(at)gmail(dot)com> wrote:
> Hi All,
>
> I was hoping somebody here could point me in the right direction.
> I am trying to duplicate changes to a specific table to a copy of that table.
>
> Does that make sense?

It depends on your application aim.
I would go for a replication solution, since this seems to me your
case and probably you will end up requiring to replicate more than one
table.
Another solution could be to do a dump/restore of the table using a
cron job or alike.

If you want to do it via trigger the most complex case is the update
one. Something like this is the base (not tested):

CREATE OR REPLACE FUNCTION duplicate_rows()
RETURNS trigger AS
$BODY$
DECLARE

BEGIN

-- if executing for a single column then compute the path
IF TG_OP = 'UPDATE' THEN
UPDATE table_copy SET field1 = NEW.field1, field2 = NEW.field2, ...
WHERE pk = NEW.pk;
ELSE IF TG_OP = 'INSERT' THEN
INSERT INTO table_copy
SELECT * FROM NEW;
END IF;

RETURN NEW;

END IF;