Below is a dummy query that returns no records:

WITH no_records AS (
  SELECT usesysid AS id, usename AS value
  FROM pg_user
  WHERE usename = 'non-existent-user'
)
SELECT
  jsonb_build_object(
    'id', id,
    'value', value
  )
FROM no_records;

Result:

 jsonb_build_object
--------------------
(0 rows)

But when I wrap the same query in a Postgres 13 procedure with an INOUT parameter

CREATE OR REPLACE PROCEDURE get_something(
  INOUT result JSONB DEFAULT NULL
)
LANGUAGE SQL
AS $$
WITH null_record AS (
  SELECT usesysid AS id, usename AS value
  FROM pg_user
  WHERE usename = 'non-existent-user'
)
SELECT
  jsonb_build_object(
    'id', id,
    'value', value
  )
  FROM null_record;
$$;

... and I call it

CALL get_something();

... I get the following error:

ERROR: procedure returned null record

DB fiddle

CodePudding user response：

with a as(
  SELECT usesysid AS id, usename AS value
  FROM pg_user
  WHERE usename = 'non-existent-user')
select row_to_json(a.*) from a;

It will return

 row_to_json
-------------
(0 rows)

There is nothing there, it's not null, null is value undefined. But null is something. here it's nothing.

CREATE PROCEDURE test_proc1(inout JSONB DEFAULT NULL)
LANGUAGE plpgsql
AS $$
BEGIN
NULL;
END;
$$;

call it: call test_proc1(null::jsonb); it will return:

 column1
---------
 [null]
(1 row)