0

I'm trying to run an insert query where it will insert to a table if the data has all parameters; else, if there are missing parameters, insert nulls as values.

An example would be some records will not have last_delivery nor volume_rate parameters. So how can I insert nulls instead into the table?

CREATE OR REPLACE FUNCTION table(
    site_id integer,
    position_id integer,
    tank_level numeric,
    tank_volume numeric,
    last_delivery numeric,
    volume_rate numeric
) RETURNS void
LANGUAGE sql
PARALLEL SAFE LEAKPROOF
AS $function$
INSERT INTO table(
    -- column names
    site_id,
    position_id,
    tank_level,
    tank_volume,
    last_delivery,
    volume_rate
) VALUES (
    -- arguments to this function
    site_id::INTEGER,
    position_id::INTEGER,
    tank_level::NUMERIC,
    tank_volume::NUMERIC,
    last_delivery::NUMERIC,
    volume_rate::NUMERIC)
$function$;

I have tried adding CASE statements as follows but no dice.

INSERT INTO table (
    -- column names
    site_id,
    position_id,
    tank_level,
    tank_volume,
    last_delivery,
    volume_rate
) VALUES (
    -- arguments to this function
    site_id::INTEGER,
    position_id::INTEGER,
    tank_level::NUMERIC,
    tank_volume::NUMERIC,
    case
        when last_delivery::numeric is null then 0
        else last_delivery::numeric
    end,
    case
        when volume_rate::NUMERIC is null then 0
        else volume_rate::NUMERIC
    end
)
5
  • It's A Bad Idea™ to call function parameters and variables the same as the column names. Also, how would "some inserts" look when they don't "have last_delivery nor volume_rate values"?
    – mustaccio
    Nov 7, 2022 at 17:43
  • The idea is that if data does not have "last_delivery nor volume_rate values" then it will insert null into those columns.
    – Jose Lopez
    Nov 7, 2022 at 17:53
  • So you're asking how to insert null values when the values are NULLs?
    – mustaccio
    Nov 7, 2022 at 18:25
  • I'm sorry, I meant if data has missing parameters.
    – Jose Lopez
    Nov 7, 2022 at 18:47
  • I've rephrased my question to ask How to insert records with all parameters and records with missing parameters.
    – Jose Lopez
    Nov 7, 2022 at 18:59

1 Answer 1

1

You can specify default values for function arguments by appending DEFAULT and the desired value to the argument declaration:

CREATE OR REPLACE FUNCTION insert_delivery(
    site_id integer DEFAULT NULL, 
    position_id integer DEFAULT NULL, 
    tank_level numeric DEFAULT NULL, 
    tank_volume numeric DEFAULT NULL,
    last_delivery numeric DEFAULT NULL, 
    volume_rate numeric DEFAULT NULL
)
RETURNS void
LANGUAGE sql
PARALLEL SAFE LEAKPROOF
AS $function$       
INSERT INTO delivery_info(
    -- column names
    site_id,
    position_id,
    tank_level,
    tank_volume,
    last_delivery,
    volume_rate
) VALUES (
    -- arguments to this function
    insert_delivery.site_id,
    insert_delivery.position_id,
    insert_delivery.tank_level,
    insert_delivery.tank_volume,
    insert_delivery.last_delivery,
    insert_delivery.volume_rate
)
$function$;

table is a reserved word, you can't create objects called table without having to double-quote it everywhere, so don't do that. I changed the function name to insert_delivery and the table name to delivery_info to avoid this problem.

As per manual on conflicts between column names and argument names:

If the argument name is the same as any column name in the current SQL command within the function, the column name will take precedence. To override this, qualify the argument name with the name of the function itself, that is function_name.argument_name. (If this would conflict with a qualified column name, again the column name wins. You can avoid the ambiguity by choosing a different alias for the table within the SQL command.)

If you rename the function, change the VALUES references to match the new name.

Remove the DEFAULT for any columns that are defined NOT NULL in your table, or change it to a more sensible value. I assume the general use of this function is with named arguments, i.e.

SELECT table(site_id=>324, tank_level=>74.0, last_delivery=>185.3)

References:

1
  • This actually worked. Thanks
    – Jose Lopez
    Nov 8, 2022 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.