another very interesting addon. pavel stehule wrote, and tom lane commited “execute using" in plpgsql.
if you ever wrote in plpgsql you know that in some cases you had to use
EXECUTE 'insert into ...
usually it was neccessary when you wanted dynamic table or column names or you were writing code like this:
SELECT * FROM TABLE WHERE FIELD LIKE some_param || '%';
the problem with execute was that you had to build your sql to plain text version, together with all parameters.
can you spell “sql-injection"? ;-P
of course you could always use quote_literal (or lately quote_nullable), but it made the code unnecessarily long.
solution was well known – placeholders.
and finally it arrived.
now you can write a code like this:
CREATE OR REPLACE FUNCTION test(use_param_name TEXT, use_param_value TEXT) RETURNS void AS $BODY$ DECLARE use_sql_update TEXT; use_sql_insert TEXT; tempint INT4; BEGIN use_sql_update := 'UPDATE setup SET value = $2 WHERE param = $1'; use_sql_insert := 'INSERT INTO setup (param, value) VALUES ($1, $2)'; EXECUTE use_sql_update USING use_param_name, use_param_value; GET DIAGNOSTICS tempint = ROW_COUNT; IF tempint = 0 THEN EXECUTE use_sql_insert USING use_param_name, use_param_value; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql;
please note that this code is perfectly safe against sql injection despite the fact that i didn't use any quote_* functions.
additional benefit is that it might be a bit faster in some cases, as “USING" avoids conversion from internal representation to text, and then from text to internal representation.