Ubaldo Quintero Ubaldo Quintero - 13 days ago 7
SQL Question

Best way to create a stored procedure that takes about 30 columns as arguments ? in postgres

What would be the best way to create a stored procedure to apply changes to the database that has about 30 arguments (columns) to be changed?

Right now I'm playing on creating a huge insert query inside the stored procedure that list all the arguments that are passed by the user. Is that the only way or is there a better way for stored procedures this big?

Example:

CREATE OR REPLACE FUNCTION "applyntunesettings"(val1,val2....val30)
RETURNS void AS
BEGIN
INSERT INTO calibrationstable (col1,col2........col20) Values (val1,val2.....val20);
INSERT INTO devicestable (col1,col2,.....col10) values (val21,val22,....val30);
END

Answer

Sometimes, row type variables can be convenient. Assuming the tables are "calibrations" and "devices" we can write:

CREATE OR REPLACE FUNCTION applyntunesettings (
    _device_name    TEXT
,   _some_value     TEXT
,   _other_value    TEXT
    -- and so on
) RETURNS void AS $$
DECLARE
    _calibration    calibrations;
    _device         devices;
BEGIN

    _device.name    := _device_name;
    INSERT INTO devices SELECT _device.*;

    _calibration.some_value     := _some_value;
    _calibration.other_value    := _other_value;
    INSERT INTO calibrations SELECT _calibration.*;

END $$ LANGUAGE plpgsql;