Inserting DEFAULT value into a column when a parameter is NULL

user_940569 0
Viewed 11960

I would like to write a stored procedure like this:

CREATE OR REPLACE FUNCTION my_function(param_1 text, param_2 text DEFAULT NULL::text) RETURNS bigint AS
$$
DECLARE ret bigint;
BEGIN
    INSERT INTO my_table(val_1, val_2) VALUES (param_1, param_2);

    -- do some more stuff

    RETURN ret;
END;
$$
LANGUAGE plpgsql;

However, I would like to use val_2 column's DEFAULT value instead of NULL - if NULL is provided as the param_2 value.

Something like this:

INSERT INTO my_table(val_1, val_2) VALUES (param_1, COALESCE(param_2, DEFAULT));

is obviously wrong, since the INSERT statement specification explicitly states an expression OR DEFAULT can be used, DEFAULT itself is not available in expressions.

I found two solutions myself but I'm not satisfied with them.

  1. Select the DEFAULT value from the information schema and use it in the COALESCE expression.

I'm no expert but it seems like there should be a simpler and more elegant way to do it.

  1. Use INSERT and then UPDATE

Like this:

-- ...
INSERT INTO my_table(val_1) VALUES (param_1)
RETURNING id INTO id_var;

IF (param_2) IS NOT NULL THEN
    UPDATE my_table SET val_2 = param_2 WHERE id = id_var;
END IF;
-- ...

There is however a catch in this solution. The actual table of the production system has some intricate triggers which run on UPDATE statements on this table so I would generally like to avoid using updates if possible.

Generally, I'll possibly stick to the second solution but that would possibly require adding some hacks to the aforementioned triggers. But if there is a way to avoid this - I will be very grateful for pointing it out.

postgresql-9.4 plpgsql postgresql stored-procedures
5 Answers

This is probably excessively heavyweight for your purpose, but it could make sense if there were lots of optional parameters, or lots of rows to be inserted:

BEGIN;
CREATE TEMPORARY TABLE temp_table (LIKE my_table INCLUDING DEFAULTS) ON COMMIT DROP;
ALTER TABLE temp_table ALTER COLUMN val_2 DROP NOT NULL; -- if necessary
INSERT INTO temp_table(val_1, val_2) VALUES (param_1, param_2);
UPDATE temp_table SET val_2 = DEFAULT WHERE val_2 IS NULL;
INSERT INTO my_table SELECT * FROM temp_table;
COMMIT;
user_8120530
Related