Date: Mon, 2 Oct 2017 14:04:38 -0500
From: Nico Williams <nico@cryptonector.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adam Brusselback <adambrusselback@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: generated columns
Message-ID: <20171002190437.GK1251@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20171002184641.GJ1251@localhost>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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