How to test if an argument is null in plpgsql function body?

Question

My function code is as below:

CREATE OR REPLACE FUNCTION INC(init_val)
RETURNS INTEGER AS $INVAL$

DECLARE CUR_VAL INTEGER;
    BEGIN
        CUR_VAL := (SELECT MAX(val_num) FROM values);
        CUR_VAL := CUR_VAL+init_val;
        INSERT INTO values VALUES (CUR_VAL);
        RETURN CUR_VAL;
    end;

    $INVAL$ LANGUAGE plpgsql

I am trying to check if init_val is null. How do you do it?

Or if you are doing this to substitute a value for NULL, then COALESCE(init_val, substitute_val). — Adrian Klaver, Jun 30 '20 at 13:49
Please tell me you are not using `select max()` to generate unique numbers. That is not going to work correctly with concurrent transactions — , Jun 30 '20 at 13:51
I am using select max() as to get the maximum value from the table and them increment it by one. number generation happens in CUR_VAL := CUR_VAL+init_val; — Kanan Mammadov, Jun 30 '20 at 13:59
Two concurrent invocations of your function will calculate the same maximum. — Laurenz Albe, Jun 30 '20 at 15:09

score 1 · Accepted Answer · answered Jul 06 '20 at 12:32

Your function exhibits multiple problems. And the description has contradictions.

This might do what you are looking for:

CREATE OR REPLACE FUNCTION f_values_inc(_init_val int)
  RETURNS integer
  LANGUAGE sql AS
$INVAL$
INSERT INTO values (val_num)
SELECT COALESCE(MAX(val_num), _init_val, 0) + 1
FROM   values
RETURNING val_num;
$INVAL$;

Call:

SELECT f_values_inc(7);

But that's just a proof of concept. I don't see the use case. The function cannot cope with concurrent writes on the table, as has been commented.

Consider a serial or IDENTITY column instead, both of which are designed not to break under concurrent write load. See:

Auto increment table column

Aside: Avoid using SQL keywords like values as identifier. While this one is basically allowed in Postgres, it's reserved in standard SQL. Use legal, lower-case, unquoted identifiers exclusively to make your life in Postgres easier. See:

Are PostgreSQL column names case-sensitive?

How to test if an argument is null in plpgsql function body?

1 Answers1