pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
few questions about rules: timestamp, new.oid
2+ messages / 2 participants
[nested] [flat]

* few questions about rules: timestamp, new.oid
@ 1999-08-02 21:04 Michael Olivier <mo@local2me.com>
  1999-08-03 10:32 ` Re: [SQL] few questions about rules: timestamp, new.oid Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Olivier @ 1999-08-02 21:04 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I am setting up rules to track changes to some tables, in PG 6.4.2-3 (RH
Linux 6.0) ... and have two questions:


1. I'm storing a datetime timestamp for when the change was entered into
my changes table, but the timestamp doesn't change with subsequent
tests:

    create rule users_modify_rule as on update to users do
        insert into changes (table_name, change_time, ref_oid, type)
        values ('users', 'now', current.oid, 'update');

dmtest=> select * from changes;
table_name|change_time                 |ref_oid|ref_name|type  
----------+----------------------------+-------+--------+------
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
(5 rows)


2. How can I get the oid of a record being inserted?  Using new.oid
isn't getting it for me... the field in my changes table remains
blank...

    create rule users_add_rule as on insert to users do
        insert into changes (table_name, change_time, ref_oid, type)
        values ('users', 'now', new.oid, 'insert');

Getting some other field, like new.acctname, does work...


Thanks,
Michael



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] few questions about rules: timestamp, new.oid
  1999-08-02 21:04 few questions about rules: timestamp, new.oid Michael Olivier <mo@local2me.com>
@ 1999-08-03 10:32 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 0 replies; 2+ messages in thread

From: Herouth Maoz @ 1999-08-03 10:32 UTC (permalink / raw)
  To: Michael Olivier <mo@local2me.com>; pgsql-sql

At 00:04 +0300 on 03/08/1999, Michael Olivier wrote:


> 1. I'm storing a datetime timestamp for when the change was entered into
> my changes table, but the timestamp doesn't change with subsequent
> tests:
>
>     create rule users_modify_rule as on update to users do
>         insert into changes (table_name, change_time, ref_oid, type)
>         values ('users', 'now', current.oid, 'update');

Better use a function than a constant - try now() instead. A constant
usually gets interpreted at the time of creation. A function remains to be
interpreted at the time of use.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-08-03 10:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-02 21:04 few questions about rules: timestamp, new.oid Michael Olivier <mo@local2me.com>
1999-08-03 10:32 ` Herouth Maoz <herouth@oumail.openu.ac.il>

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