From: Nico Williams <nico@cryptonector.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adam Brusselback <adambrusselback@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: generated columns
Date: Mon, 2 Oct 2017 14:04:38 -0500
Message-ID: <20171002190437.GK1251@localhost> (raw)
In-Reply-To: <20171002184641.GJ1251@localhost>
References: <b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com>
<20171002152338.GH1251@localhost>
<CAMjNa7fhskTTMSLMUL=e1r-EOg2Q3-cSayEDB1MADmX_zzn1gw@mail.gmail.com>
<20171002181042.GI1251@localhost>
<10550.1506969038@sss.pgh.pa.us>
<20171002184641.GJ1251@localhost>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
So yes, distinguishing stored vs. not stored computed columns is useful,
especially if the expression can refer to other columns of the same row,
though not only then.
Examples:
-- useful only if stored (assuming these never get updated)
inserted_at TIMESTAMP WITHOUT TIME ZONE AS (clock_timestamp())
-- useful only if stored
uuid uuid AS (uuid_generate_v4())
-- useful only if stored
who_done_it TEXT (current_user)
-- useful especially if not stored
user_at_host TEXT (user || '@' || host)
-- useful if stored
original_user_at_host TEXT (user || '@' || host)
I assume once set, a stored computed column cannot be updated, though
maybe being able to allow this would be ok.
Obviously all of this can be done with triggers and VIEWs... The most
useful case is where a computed column is NOT stored, because it saves
you having to have a table and a view, while support for the stored case
merely saves you having to have triggers. Of course, triggers for
computing columns are rather verbose, so not having to write those would
be convenient.
Similarly with RLS. RLS is not strictly necessary since VIEWs and
TRIGGERs allow one to accomplish much the same results, but it's a lot
of work to get that right, while RLS makes most policies very pithy.
(RLS for *update* policies, however, still can't refer to NEW and OLD,
so one still has to resort to triggers for updates in many cases).
Nico
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nico@cryptonector.com, tgl@sss.pgh.pa.us, adambrusselback@gmail.com
Subject: Re: generated columns
In-Reply-To: <20171002190437.GK1251@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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