Return NULL instead of composite value from PostgreSQL function

Question

Let us have the following things in PostgreSQL:

CREATE TYPE struct AS (x INT, y INT);
CREATE TABLE tbl (a INT, b struct);

CREATE FUNCTION find_tbl_entry(clear BOOL) RETURNS tbl AS $$
DECLARE k tbl;
BEGIN
  IF clear THEN
    k.b := NULL;
  END IF;

  RETURN k;
END;
$$ LANGUAGE plpgsql;

That is, we have a function returning a value of the composite type tbl, which in turn has an attribute b of composite type struct as one of its attributes. (The original problem is more interesting - a translating function returning a row with some attributes translated accordingly; the problem boils down to the presented code, though.)

SELECT find_tbl_entry(FALSE) results in (,(,)), i.e., NULL as the value of a and an empty struct (pair of NULL and NULL) as the value of b, which is somewhat expected.

Now, even SELECT find_tbl_entry(TRUE) results in (,(,)), i.e., even if the b attribute is explicitly set to NULL, the result is not NULL, but it is still the empty struct.

What can I do for the function find_tbl_entry to return NULL in the b attribute?

EDIT: As it turns out, the strange thing is the assignment k.b := NULL. When extending the function:

k.b := NULL;
RAISE NOTICE '%', k.b IS DISTINCT FROM NULL;

it emits "NOTICE: t". Thus, it seems assigning NULL to a composite value actually assigns a composite having all attributes NULL. Which is quite strange, considering the fact that NULL values are distinguishable from (NULL,NULL) when stored in a table (UPDATE tbl SET b = NULL results in b IS NOT DISTINCT FROM NULL holding for each row; on the other hand, UPDATE tbl SET b = (NULL,NULL) is false for that test).

Clodoaldo Neto · Accepted Answer · 2017-03-08 10:09:43Z

1

It is necessary to either:

Return the null directly in instead of returning the assigned variable:

create or replace function find_tbl_entry()
returns tbl as $$
declare s struct;
begin
    s := null;
    return (1, nullif(s, (null,null)::struct));
end;
$$ language plpgsql;

select a, b, b is null from find_tbl_entry();
 a | b | ?column? 
---+---+----------
 1 |   | t

Or compare at function usage time:

select coalesce(nullif((find_tbl_entry()).b, (null,null)::struct), (1,2)::struct);
 coalesce 
----------
 (1,2)

edited Mar 8, 2017 at 10:09

answered Mar 7, 2017 at 13:50

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ondřej Bouda Over a year ago

Do you happen to know of a variant of the first option which would be defensive against modifications of tbl columns? I mean, when an extra column is added to tbl, it would be nice for the function not to break - especially when the problem only appears at run time, when the function actually gets called.

Clodoaldo Neto Over a year ago

@OndřejBouda Edited

Ondřej Bouda Over a year ago

Thanks for editing, the code does not seem to be correct, though (RETURN should have no parameter in a function returning a table), and speaking of types, it returns a table rather than a single row. I actually get the point, however. Simply defining the return type as a new composite type t and returning it, using the proposed construct return (1, nullif(s, (null,null)::struct)); resolves the problem. Could you fix the edited version, please, so that I could accept the answer?

Clodoaldo Neto Over a year ago

@OndřejBouda Fixing the returns table version gives the same problem back. So you are out of options.

Laurenz Albe · Accepted Answer · 2017-03-07 12:00:57Z

1

In SQL, a composite value (the SQL standard call this a “value of degree > 1”) is NULL when all its components are NULL, so PostgreSQL is behaving correctly.

ISO/IEC 9075-2:2003, chapter 8, verse 7, sayeth:

8.7 <null predicate>

Function

Specify a test for a null value.

Format
<null predicate> ::= <row value predicand> <null predicate part 2>
<null predicate part 2> ::= IS [ NOT ] NULL
Syntax Rules

None.

Access Rules

None.

General Rules

1) Let R be the value of the <row value predicand>.

2) Case:

    a) If R is the null value, then “R IS NULL” is True.

    b) Otherwise:

        i) The value of “R IS NULL” is

            Case:

            1) If the value of every field in R is the null value, then True.

            2) Otherwise, False.

        ii) The value of “R IS NOT NULL” is

            Case:

            1) If the value of no field in R is the null value, then True.

            2) Otherwise, False.

(If you think that this is crazy, you are not alone.)

You can verify that PostgreSQL handles such a value correctly:

SELECT (ROW(NULL,ROW(NULL,NULL))::tbl).b IS NULL;
 ?column?
----------
 t
(1 row)

I understand that you would rather like the value to read (NULL, NULL), but you cannot get that with PostgreSQL. I hope it is a comfort to you that it will nonetheless behave correctly.

answered Mar 7, 2017 at 12:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

Ondřej Bouda Over a year ago

Thank you for your answer. However, it does not quite solve the exact question. I have already played with it, finding out that (NULL,NULL) IS NULL returns true. No doubt Postgres behaves correctly, either. Originally, I used COALESCE((find_tbl_entry(...)).b, X), where I wanted X to be returned if the b attribute was NULL. Unexpectedly, it kept returning (NULL,NULL) instead of X, which is because COALESCE apparently uses IS DISTINCT FROM NULL for testing which value to return. I want to write a function safe for COALESCE, which will be when b is returned as NULL.

Ondřej Bouda Over a year ago

...and, despite the practical issues, I was also curious about the case when I assign NULL to the b field and still get (NULL,NULL) in the returned value. Which really seems strange, despite the definition of the IS NULL operator.

Laurenz Albe Over a year ago

Yes, it is a mess. There have been discussions how to improve it, but no good direction forward has been found.

Collectives™ on Stack Overflow

Return NULL instead of composite value from PostgreSQL function

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related