pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedon insert rule with default value
3+ messages / 2 participants
[nested] [flat]
* on insert rule with default value
@ 2012-02-21 20:51 Ron Peterson <rpeterso@mtholyoke.edu>
2012-02-22 13:52 ` Re: on insert rule with default value Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Peterson @ 2012-02-21 20:51 UTC (permalink / raw)
To: pgsql-sql
My rule below does not insert the the same uuid value into the test_log
table as is created in the test table when I insert a new value. I know
I've worked through this before, but I'm not remembering why this is.
What's a right way to do this?
create table test (
anid
uuid
not null
default encode( gen_random_bytes( 16 ), 'hex' )::uuid
primary key,
value
text
);
create table test_log (
anid
uuid,
value
text,
op
text,
attime
timestamp with time zone
);
create rule test_rule_a as
on insert to test do (
insert into test_log ( anid, value, op, attime )
values ( new.anid, new.value, 'insert', now() )
);
--
Ron Peterson
Network & Systems Administrator
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: on insert rule with default value
2012-02-21 20:51 on insert rule with default value Ron Peterson <rpeterso@mtholyoke.edu>
@ 2012-02-22 13:52 ` Ron Peterson <rpeterso@mtholyoke.edu>
2012-02-22 15:32 ` Re: on insert rule with default value Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Peterson @ 2012-02-22 13:52 UTC (permalink / raw)
To: pgsql-sql
2012-02-21_15:51:30-0500 Ron Peterson <rpeterso@mtholyoke.edu>:
> My rule below does not insert the the same uuid value into the test_log
> table as is created in the test table when I insert a new value. I know
> I've worked through this before, but I'm not remembering why this is.
> What's a right way to do this?
Obviously I can use a trigger function. I'm mostly wondering if there
are any tricks to accomplishing this with rules (I like the simple
syntax). I suspect the problem here is that 'new' on insert refers to
the function used to calculate the new value, /not/ the new value that
is actually inserted into the table. There are probably reasons for
that; but it would seem nicer to refer to the actual new table value
rather than the expression used to calculate it. My 2c.
-Ron-
> create table test (
> anid
> uuid
> not null
> default encode( gen_random_bytes( 16 ), 'hex' )::uuid
> primary key,
> value
> text
> );
>
> create table test_log (
> anid
> uuid,
> value
> text,
> op
> text,
> attime
> timestamp with time zone
> );
>
> create rule test_rule_a as
> on insert to test do (
> insert into test_log ( anid, value, op, attime )
> values ( new.anid, new.value, 'insert', now() )
> );
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: on insert rule with default value
2012-02-21 20:51 on insert rule with default value Ron Peterson <rpeterso@mtholyoke.edu>
2012-02-22 13:52 ` Re: on insert rule with default value Ron Peterson <rpeterso@mtholyoke.edu>
@ 2012-02-22 15:32 ` Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2012-02-22 15:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: Ron Peterson <rpeterso@mtholyoke.edu>
On Wednesday, February 22, 2012 5:52:39 am Ron Peterson wrote:
> 2012-02-21_15:51:30-0500 Ron Peterson <rpeterso@mtholyoke.edu>:
> > My rule below does not insert the the same uuid value into the test_log
> > table as is created in the test table when I insert a new value. I know
> > I've worked through this before, but I'm not remembering why this is.
> > What's a right way to do this?
>
> Obviously I can use a trigger function. I'm mostly wondering if there
> are any tricks to accomplishing this with rules (I like the simple
> syntax). I suspect the problem here is that 'new' on insert refers to
> the function used to calculate the new value, /not/ the new value that
> is actually inserted into the table. There are probably reasons for
> that; but it would seem nicer to refer to the actual new table value
> rather than the expression used to calculate it. My 2c.
The simplest explanation I could find is here:
http://www.postgresql.org/docs/9.0/interactive/querytree.html
"the target list...
For INSERT commands, the target list describes the new rows that should go into
the result relation. It consists of the expressions in the VALUES clause or the
ones from the SELECT clause in INSERT ... SELECT. The first step of the rewrite
process adds target list entries for any columns that were not assigned to by
the original command but have defaults. Any remaining columns (with neither a
given value nor a default) will be filled in by the planner with a constant null
expression.
"
If you want all the gory details read through section 37, in particular 37.3 :)
The above is why I use triggers now. It is a lot easier to follow the logic in a
trigger than in a rule.
>
> -Ron-
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-02-22 15:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-21 20:51 on insert rule with default value Ron Peterson <rpeterso@mtholyoke.edu>
2012-02-22 13:52 ` Ron Peterson <rpeterso@mtholyoke.edu>
2012-02-22 15:32 ` Adrian Klaver <adrian.klaver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox