Thread: Question on Rules

Question on Rules

From
Date:
I am creating a rule which will copy a record when it is changed to a
audittable.  My question is that the first column is a UUID data type
with a defined as auditaccessorid uuid DEFAULT
isscontrib.uuid_generate_v4() NOT NULL,

Right now I've got that set to NULL to allow the parser to "compile".
What value should I have in here since I want a newly created UUID?

CREATE RULE log_accessor AS ON UPDATE TO iss.accessor
    WHERE NEW.* <> OLD.*
    DO INSERT INTO iss.auditaccessor VALUES (NULL,
                                          'C',
                                          new.loaddtlid,
                                          new.seqno,
                                          new.billable,
                                          new.payind,
                                          new.code,
                                          new.description,
                                          new.ref,
                                          new.tractororcarrierflag,
                                          new.tractororcarrierno,
                                          new.tractorpct,
                                          new.charge,
                                          new.type,
                                          new.checkdate,
                                          new.checkno,
                                          new.processed,
                                          new.itemflag,
                                          new.tractortermloc,
                                          new.cost,
                                          new.batchno,
                                          new.editdatetime,
                                          new.edituser);

Best Regards,

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978


Re: Question on Rules

From
"David Johnston"
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
mgould@isstrucksoftware.net
Sent: Saturday, February 18, 2012 5:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Question on Rules

I am creating a rule which will copy a record when it is changed to a audittable.  My question is that the first column
isa UUID data type with a defined as auditaccessorid uuid DEFAULT 
isscontrib.uuid_generate_v4() NOT NULL,

Right now I've got that set to NULL to allow the parser to "compile".
What value should I have in here since I want a newly created UUID?

CREATE RULE log_accessor AS ON UPDATE TO iss.accessor
    WHERE NEW.* <> OLD.*
    DO INSERT INTO iss.auditaccessor VALUES (NULL,
                                          'C',
                                          new.loaddtlid,
                                          new.seqno,
                                          new.billable,
                                          new.payind,
                                          new.code,
                                          new.description,
                                          new.ref,
                                          new.tractororcarrierflag,
                                          new.tractororcarrierno,
                                          new.tractorpct,
                                          new.charge,
                                          new.type,
                                          new.checkdate,
                                          new.checkno,
                                          new.processed,
                                          new.itemflag,
                                          new.tractortermloc,
                                          new.cost,
                                          new.batchno,
                                          new.editdatetime,
                                          new.edituser);

Best Regards,

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978


--------------------------------------------------------------------

INSERT INTO table (serial_col1) VALUES (DEFAULT);

Also, I presume you have a good reason for using a RULE instead of a TRIGGER?  If not you should default to a TRIGGER
forthis kind of behavior. 

David J.



Re: Question on Rules

From
Date:
<table align="center" bgcolor="#ffffff" border="0" cellpadding="0" cellspacing="0" style="empty-cells: show;"
width="100%"><tr><td;=";" </td="</td" align="left" background="cid:top@58571d753b9e554223bb4a1866f4c8b1" colspan="1"
height="72"style="text-align: left; background-repeat: no-repeat;"></td></tr><tr><td style="vertical-align: top;"><div
style="padding:5px; overflow-x: auto;"></div></td></tr></table><span style="font-family:Verdana; color:#000000;
font-size:10pt;"><div>David,</div><div> </div><div>Arerules deprecated?<br /></div><div><br /></div><div>Michael
Gould</div><div>IntermodalSoftware Solutions, LLC</div><div>904-226-0978</div><div> </div><div> </div><blockquote
id="replyBlockquote"style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black;
MARGIN-LEFT:8px; FONT-SIZE: 10pt" webmail="1"><div id="wmQuoteWrapper">-------- Original Message --------<br />Subject:
Re:[GENERAL] Question on Rules<br />From: "David Johnston" <<a
href="mailto:polobo@yahoo.com">polobo@yahoo.com</a>><br/>Date: Sun, February 19, 2012 9:16 pm<br />To: <<a
href="mailto:mgould@isstrucksoftware.net">mgould@isstrucksoftware.net</a>>,<<a
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br/><br />-----Original Message-----<br
/>From:<a href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a> [<a
href="mailto:pgsql-general-owner@postgresql.org">mailto:pgsql-general-owner@postgresql.org</a>]On Behalf Of <a
href="mailto:mgould@isstrucksoftware.net">mgould@isstrucksoftware.net</a><br/>Sent: Saturday, February 18, 2012 5:17
AM<br/>To: <a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br />Subject: [GENERAL]
Questionon Rules<br /><br />I am creating a rule which will copy a record when it is changed to a audittable. My
questionis that the first column is a UUID data type with a defined as auditaccessorid uuid DEFAULT<br
/>isscontrib.uuid_generate_v4()NOT NULL,<br /><br />Right now I've got that set to NULL to allow the parser to
"compile".<br />What value should I have in here since I want a newly created UUID?<br /><br />CREATE RULE log_accessor
ASON UPDATE TO iss.accessor<br />WHERE NEW.* <> OLD.*<br />DO INSERT INTO iss.auditaccessor VALUES (NULL,<br
/>'C',<br/>new.loaddtlid, <br />new.seqno, <br />new.billable,<br />new.payind,<br />new.code,<br />new.description,
<br/>new.ref,<br />new.tractororcarrierflag, <br />new.tractororcarrierno,<br />new.tractorpct,<br />new.charge,<br
/>new.type,<br/>new.checkdate,<br />new.checkno,<br />new.processed,<br />new.itemflag, <br />new.tractortermloc,<br
/>new.cost,<br/>new.batchno,<br />new.editdatetime, <br />new.edituser);<br /><br />Best Regards,<br /><br />Michael
Gould<br/>Intermodal Software Solutions, LLC<br />904-226-0978<br /><br /><br
/>--------------------------------------------------------------------<br/><br />INSERT INTO table (serial_col1) VALUES
(DEFAULT);<br/><br />Also, I presume you have a good reason for using a RULE instead of a TRIGGER? If not you should
defaultto a TRIGGER for this kind of behavior.<br /><br />David J.<br /><br /><br /><br />-- <br />Sent via
pgsql-generalmailing list (<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br />To make
changesto your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br
/></div></blockquote></span>

Re: Question on Rules

From
John R Pierce
Date:
On 02/20/12 9:07 AM, mgould@isstrucksoftware.net wrote:
> Are rules deprecated?

rules are very tricky, and primarily exist now for internal use.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: Question on Rules

From
Michael Gould
Date:
Thanks I will change to a trigger

Best Regards

Mike Gould

Sent from Samsung mobile

David Johnston <polobo@yahoo.com> wrote:

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
mgould@isstrucksoftware.net
>Sent: Saturday, February 18, 2012 5:17 AM
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Question on Rules
>
>I am creating a rule which will copy a record when it is changed to a audittable.  My question is that the first
columnis a UUID data type with a defined as auditaccessorid uuid DEFAULT 
>isscontrib.uuid_generate_v4() NOT NULL,
>
>Right now I've got that set to NULL to allow the parser to "compile".
>What value should I have in here since I want a newly created UUID?
>
>CREATE RULE log_accessor AS ON UPDATE TO iss.accessor
>    WHERE NEW.* <> OLD.*
>    DO INSERT INTO iss.auditaccessor VALUES (NULL,
>                                          'C',
>                                          new.loaddtlid,
>                                          new.seqno,
>                                          new.billable,
>                                          new.payind,
>                                          new.code,
>                                          new.description,
>                                          new.ref,
>                                          new.tractororcarrierflag,
>                                          new.tractororcarrierno,
>                                          new.tractorpct,
>                                          new.charge,
>                                          new.type,
>                                          new.checkdate,
>                                          new.checkno,
>                                          new.processed,
>                                          new.itemflag,
>                                          new.tractortermloc,
>                                          new.cost,
>                                          new.batchno,
>                                          new.editdatetime,
>                                          new.edituser);
>
>Best Regards,
>
>Michael Gould
>Intermodal Software Solutions, LLC
>904-226-0978
>
>
>--------------------------------------------------------------------
>
>INSERT INTO table (serial_col1) VALUES (DEFAULT);
>
>Also, I presume you have a good reason for using a RULE instead of a TRIGGER?  If not you should default to a TRIGGER
forthis kind of behavior. 
>
>David J.
>
>
>