Re: Rule Question

The original post was related to the update of b, so I guess it is
better to limit the trigger scope to update on such column:

CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS
 $mirror$
 BEGIN
 NEW.a = NEW.b;
 RETURN NEW;
 END;
 $mirror$ LANGUAGE plpgsql;

CREATE TRIGGER tr_b_mirror AFTER UPDATE OF b ON cats FOR EACH ROW
EXECUTE PROCEDURE b_mirror();

It is worth noting that the trigger could be an after one, since if I
get it right the tuple has to be always updated, and there is no
condition that prevents that. Moreover, it is possible to check for
null values as in the suggested example below.

Luca


On Thu, Jul 25, 2013 at 1:18 PM, Giuseppe Broccolo
<giuseppe.broccolo@2ndquadrant.it> wrote:
>
>> I am trying to do something like this
>>
>> create table cats (a text,b text);
>>
>> create rule cats_test as on update to cats do set a = new.b;
>>
>> Can i manipulate column "a" sort of like this...  or is  there a better
>> way.
>
> I think the easiest way to do this is to use a trigger like this:
>
> CREATE FUNCTION update_column() RETURNS TRIGGER AS $update_column$
>     BEGIN
>         IF TG_OP = 'INSERT' OR
>             (TG_OP = 'UPDATE' AND
>                 (NEW.b != OLD.b OR
>                     (NEW.b IS NULL AND OLD.b IS NOT NULL) OR
>                     (NEW.b IS NOT NULL AND OLD.b IS NULL)
>                 )
>         ) THEN
>             NEW.a = NEW.b;
>         END IF;
>         RETURN NEW;
>     END;
> $update_column$ LANGUAGE plpgsql;
>
> CREATE TRIGGER update_column BEFORE INSERT OR UPDATE ON cats
>     FOR EACH ROW
>     EXECUTE PROCEDURE update_column();
>
> So for instance, if you insert a new "column b" value
>
> INSERT INTO cats (b) VALUES ('byebye');
>
> you'll get a='byebye' and b='byebye', and if you update this value
>
> UPDATE cats SET b = 'goodbye' WHERE a = 'byebye';
>
> you'll get a='goodbye' and b='goodbye'. Anyway, this is just an example. I
> suggest that you look at the CREATE TRIGGER page in the documentation
>
> http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html
>
> as you can also consider conditional triggers to be executed, for example,
> only when the b column is updated.
>
> Hope it can help.
>
> Giuseppe.
>
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general