Stored Procedure to Update Table with a Variable Column Name-CodePudding

I am trying the write a stored procedure in plpgsql to simply update an integer value in a table. I have a table (called conditions) with four columns of type integer called structure, missing, repair, and cosmetic that accept 0, 1, 0r 2 (see table below).

 id | factor_id | structure | missing | repair | cosmetic 
---- ----------- ----------- --------- -------- ----------
  2 |         4 |         0 |       0 |      1 |        2
  3 |         5 |         0 |       0 |      0 |        0
  4 |         6 |         0 |       0 |      0 |        1
  5 |         7 |         1 |       0 |      0 |        1
  6 |         8 |         1 |       0 |      0 |        1

I want to write one update procedure to handle these three columns so I need to run some dynamic sql. Here is the update statement:

UPDATE conditions SET (structure/missing/repair/cosmetic) = 1 WHERE id = 2 AND factor_id = 4;

Based on the posts I have seen, I wrote this code with an EXECUTE format function:

CREATE OR REPLACE PROCEDURE spup_conditions
(
    _col VARCHAR,
    _parcel INT,
    _factor INT,
    _value INT
)
AS $$
DECLARE
    _colval text := _col;
BEGIN
    EXECUTE
        format('UPDATE conditions SET %I = $1 WHERE parcel_id = $2 AND
        factor_id = $3', _colval)
    USING _value, _parcel, _factor;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

The procedure was accepted by PostgreSQL but when I call it

CALL spup_conditions('cosmetic', 1, 2, 4)

PostgreSQL says the procedure was called but when I look at the table, the value hasn't changed. I appreciate your help.

CodePudding user response：

If you add parameter names, you will see that your procedure is trying to perform the following update

CALL spup_conditions('cosmetic', 1, 2, 4);

CALL spup_conditions(
        _col => 'cosmetic',
        _parcel => 1,
        _factor => 2,
        _value => 4);

    UPDATE conditions
       SET cosmetic = 4
     WHERE parcel_id = 1
       AND factor_id = 2;

There are no rows in your table that meet these conditions.

CodePudding user response：

There were 2 errors in your proc and call.

where clause there is no parcel_id column - change to id

format('UPDATE conditions_so SET %I = $1 WHERE id = $2 AND factor_id = $3', _colval)

for your test data there is no row for id=1

call spup_conditions('cosmetic', 2, 4, 4);