Re: Auto-updated fields

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: David Fetter <david(at)fetter(dot)org>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Auto-updated fields
Date: 2008-05-08 09:56:28
Message-ID: 4822CE4C.8080006@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Martijn van Oosterhout írta:
> On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote:
>
>> 1. Create a generic (possibly overloaded) trigger function, bundled
>> with PostgreSQL, which sets a field to some value. For example, a
>> timestamptz version might set the field to now().
>>
>
> Doesn't the SQL standard GENERATED BY functionality work for this? Or
> won't that handle updates?
>

You mean GENERATED ALWAYS AS (expression)?
Yes, they should be updated on every UPDATE as the expression
may include other fields in the same row.

A GENERATED column implemented as a stored column would
work for this but a virtual column would not. A virtual column
would return different values for "now()" in every SELECT.

However we can argue for use cases of a virtual column and implement
it similarly as VIEWs, i.e an ON SELECT rule can expand the original
expression of the column definition.

I suggest using these syntaxes if we decide to implement them:

GENERATED ALWAYS AS (expression) -- SQL standard stored GENERATED column
GENERATED VIRTUAL AS (expression) -- virtual column, obviously

> Have a nice day,
>

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zoltan Boszormenyi 2008-05-08 10:03:55 Re: Auto-updated fields
Previous Message Magnus Hagander 2008-05-08 09:00:09 Re: pgsql: Make the pg_stat_activity view call a SRF