postgresql - How can I create a dynamic update query in Postgres with plpgsql? - Stack Overflow

admin管理员组

文章数量:1122832

I want to build a query that can dynamically update tables, that is, receive a json with the fields that need to be updated.

I have something like this:

CREATE OR REPLACE FUNCTION dynamic_update_multiple(
    table_name text,
    updates jsonb,
    id_value integer
) RETURNS void AS
$$
DECLARE
    set_clauses text = '';
    key text;
    value text;
BEGIN
    FOR key, value IN SELECT * FROM jsonb_each_text(updates) LOOP
        set_clauses := set_clauses || format('%I = %L, ', key, value);
    END LOOP;

    set_clauses := left(set_clauses, length(set_clauses) - 2);

    EXECUTE format('UPDATE %I SET %s WHERE id = $1', table_name, set_clauses)
    USING id_value;
END;
$$ LANGUAGE plpgsql;

But I have not been able to test the function because when I create it I get this error:

SQL Error [42601]: Unterminated string literal started at position 0 in SQL ' LANGUAGE plpgsql;

What should I correct in my function?

本文标签： postgresqlHow can I create a dynamic update query in Postgres with plpgsqlStack Overflow