2

Suppose a table defined like this :

CREATE TABLE clients (
    active BOOLEAN
    ,deceased BOOLEAN
);

I define a function specific to that table (I don't remember how this is called nor can I find it in the CREATE FUNCTION documentation or search engine) :

CREATE FUNCTION isvisible(clients) RETURNS BOOLEAN
AS $BODY$
BEGIN
  RETURN $1.active AND NOT $1.deceased;
END;
$BODY$ LANGUAGE plpgsql STABLE;

I then get the list of clients with :

SELECT * FROM clients c WHERE c.isvisible;

In the actual table, there are several fields that could change the visible status of the record (mainly in the list of clients). I am planning to have such function so that the visible status is consistent across the application.

  1. Is it safe to declare such a function as IMMUTABLE ? I am wondering because any real field (ie: active or deceased) could potentially change within a transaction.
  2. I would like to minimize the performance degradation that such function might induce. And since I have several cases where I'd like to implement such helper functions, I'd like to hear your advices before generalizing the concept.
JC Boggio
  • 367
  • 1
  • 11
  • 1
    BTW, what is the name of such functions so that I can search information ? – JC Boggio Jul 07 '20 at 09:37
  • This PostgreSQL oddity is called `attribute notation`. You can find explanations and examples from [this answer](https://stackoverflow.com/questions/11165450/store-common-query-as-column/11166268#11166268) and [this one](https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql) – JC Boggio Jul 09 '20 at 10:34
  • In PostgreSQL's documentation, it can be found [at the end of the 8.16.5 paragraph](https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE) – JC Boggio Jul 09 '20 at 10:43

1 Answers1

1

If you are concerned about performance, you should make that a language sql function so it can be inlined

CREATE FUNCTION isvisible(p_row clients) 
  RETURNS BOOLEAN
AS $BODY$
  select p_row.active AND NOT p_row.deceased;
$BODY$ 
LANGUAGE sql 
STABLE;

Even if it's not inlined, a SQL function is still more efficient then a PL/pgSQL function (see e.g. here)

As pointed out by Laurenz Albe in the comments, it is safe to mark that function immutable.

  • I agree with the SQL function for inling, but the function is *definitely* `IMMUTABLE`. – Laurenz Albe Jul 07 '20 at 09:31
  • @LaurenzAlbe: hmm, so passing a row to a function isn't considered a "database lookup"? –  Jul 07 '20 at 09:53
  • 2
    No, `clients` is a composite type. The function does not look up the table, it just extracts the elements from the composite type. – Laurenz Albe Jul 07 '20 at 09:59