Date: Wed, 22 Feb 2012 08:52:39 -0500
From: Ron Peterson <rpeterso@mtholyoke.edu>
To: pgsql-sql@postgresql.org
Subject: Re: on insert rule with default value
Message-ID: <20120222135239.GI29194@mtholyoke.edu>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20120221205130.GG29194@mtholyoke.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20120221205130.GG29194@mtholyoke.edu>
Organization: Mount Holyoke College
User-Agent: Mutt/1.5.20 (2009-06-14)

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() )
> );