PostgreSQL plpgsql function issues

Question

I have a function in my PostgreSQL 16.1 database running on Debian 12.2:

CREATE OR REPLACE FUNCTION ref.lookup_xxx(
    in_code character varying,
    in_description character varying)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
  declare id_val integer;
  begin
    if in_code is null then -- nothing to do
      return null;
    end if;
    -- check if code is already present in the table:
    id_val = (select min(id) from ref.xxx where code = in_code);
    if id_val is null then -- insert new code, desc into reference table:
      insert into ref.xxx (code, description) values (in_code, in_description) returning id_val;
    end if;
    return id_val; -- return id of new or existing row
  exception
    when others then
      raise exception 'lookup_xxx error code, desc = %, %', in_code, in_description;
  end; 
$BODY$;

It returns an error:

ERROR:  lookup_xxx error code, desc = 966501, <NULL>
CONTEXT:  PL/pgSQL function ref.lookup_xxx(character varying,character varying) line 15 at RAISE 

SQL state: P0001

If I run the ad hoc query below, it succeeds:

insert into ref.xxx (code, description) values ('966501', null);

I can't get this ad hoc query to run - it may not be possible:

do $$
declare x integer;
begin
  insert into ref.xxx (code, description) values ('966501', null) returning x;
  raise notice 'x is %', x;
end; 
$$

I'm looking for any suggestions to correct the function - I've reviewed the postgres docs and can't find anything helpful. Stepping through the function in the debugger shows it failing at the insert statement. I've got similar queries in other plpgsql functions that are working correctly.

Answer 1

Looks like you need this: RETURNING id INTO id_val;

CREATE OR REPLACE FUNCTION ref.lookup_xxx(
    in_code CHARACTER VARYING,
    in_description CHARACTER VARYING)
    RETURNS INTEGER
    LANGUAGE 'plpgsql'
    COST 10
    VOLATILE PARALLEL UNSAFE
AS
$BODY$
DECLARE
    id_val INTEGER;
BEGIN
    IF in_code IS NULL THEN -- nothing to do
        RETURN NULL;
    END IF;
    -- check if code is already present in the table:
    id_val = (SELECT MIN(id) FROM ref.xxx WHERE code = in_code);
    IF id_val IS NULL THEN -- insert new code, desc into reference table:
        INSERT INTO ref.xxx (code, description) 
        VALUES (in_code, in_description) 
        RETURNING id 
            INTO id_val; -- <-- this one
    END IF;
    RETURN id_val; -- return id of new or existing row
EXCEPTION -- Why? You will be hiding the real error
    WHEN OTHERS THEN
        RAISE EXCEPTION 'lookup_xxx error code, desc = %, %', in_code, in_description;
END;
$BODY$;