Обсуждение: Rule Question

On Wed, Jul 24, 2013 at 11:44 PM, Andrew Bartley <ambartley@gmail.com> wrote:
> Hope this question is not too stupid but......
>
> 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 would like to do this as the construction of the new.b value is complex
> and time consuming, so I just want to do it once.
>
> update cats
> set b = something_complex_and_time_consuming(b);

AFAIK, the best way here is to use trigger that does new.a = new.b.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com

On Thu, Jul 25, 2013 at 8:44 AM, Andrew Bartley <ambartley@gmail.com> wrote:

> create rule cats_test as on update to cats do set a = new.b;
>

I would use a column trigger attached to the 'a' column. Rules are
better for query rewriting rather than from semantic changes.
That's my opinion.

Luca

> 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

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

Luca Ferrari <fluca1978@infinito.it> writes:
> 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,

No, it has to be a BEFORE trigger, else it's too late to affect the
row value that gets stored.  Other than that I think this is the
best solution --- there's no reason to make the trigger any more
complicated than this.

BTW, I didn't see anyone pointing out the real reason why a rule isn't
a suitable solution for the OP's problem.  Namely, that a rule is a
macro, so if you have

create rule cats_test as on update to cats do set a = new.b;

the effect of that will be that the *expression* for the new value of b
will be inserted into the rule.  So you'll end up with double evaluation
of that expression, exactly what he wanted to avoid.  A trigger is
handed the fully calculated intended-new-row value, so it doesn't have
this issue.

            regards, tom lane

On Thu, Jul 25, 2013 at 3:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Luca Ferrari <fluca1978@infinito.it> writes:
>> 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,
>
> No, it has to be a BEFORE trigger, else it's too late to affect the
> row value that gets stored.

Ops..I wrote it without my brain with me: of course it has to be a before one.

Thanks,
Luca

> Unrelated to the OP's question, the suggestion above could be more
> simply rewritten as
>
> TG_OP = 'UPDATE'
> AND NEW.b IS DISTINCT FROM OLD.b
You're right! :)

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it