postgresqlstored-proceduresinout

Why is Postgres throwing "ERROR: procedure returned null record"?


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


Solution

  • 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)