Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx
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$
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;
$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.