Stored Procedure to Update Table with a Variable Column Name

Question

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.

UPDATE here is my revised table and procedure and two other actions i added

 id | parcel_id | factor_id | visible | structure | missing | repair | cosmetic | status | completed 
----+-----------+-----------+---------+-----------+---------+--------+----------+--------+-----------
  1 |        71 |         3 | f       |         0 |       0 |      1 |        0 | f      | 
  2 |        71 |         4 | f       |         0 |       0 |      1 |        2 |        | 
  3 |        71 |         5 | f       |         0 |       0 |      0 |        0 |        | 
  4 |        71 |         6 | f       |         0 |       0 |      0 |        1 |        | 
  5 |        71 |         7 | f       |         1 |       0 |      0 |        1 |        | 
  6 |        71 |         8 | f       |         1 |       0 |      0 |        1 |        | 
  7 |        71 |         9 | f       |         0 |       0 |      0 |        1 |        | 
  8 |        71 |        10 | f       |         0 |       0 |      1 |        2 |        | 
  9 |        71 |        11 | f       |         0 |       0 |      0 |        1 |        | 
 10 |        71 |        12 | t       |         0 |       0 |      0 |        0 |        | 
 11 |        71 |        13 | f       |         0 |       0 |      0 |        1 |        | 
 12 |        71 |        14 | f       |         0 |       0 |      0 |        1 |        | 
 13 |        71 |        15 | f       |         0 |       0 |      0 |        0 |        | 
 14 |        71 |        16 | f       |         0 |       0 |      0 |        1 |        | 

CREATE OR REPLACE PROCEDURE blight.spup_conditions
(
    _col text,
    _value INT,
    _parcel INT,
    _factor INT

)
AS $$
DECLARE
    _colval text := _col;
BEGIN
    EXECUTE
    format('UPDATE blight.conditions SET %I = $1
        WHERE parcel_id = $2 AND factor_id = $3', _colval)
    USING _value, _parcel, _factor;
    EXECUTE
    format('UPDATE blight.conditions SET status = FALSE
        WHERE parcel_id = $1 AND factor_id = $2')
    USING _parcel, _factor;
    EXECUTE
    format('update blight.conditions set completed = NULL
        WHERE parcel_id = $1 AND factor_id = $2')
    USING _parcel, _factor;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

score 0 · Answer 1 · answered May 03 '22 at 05:25

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.

score 0 · Accepted Answer · answered May 03 '22 at 05:26

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);

Stored Procedure to Update Table with a Variable Column Name

2 Answers2