My motivation for implementation USING feature in EXECUTE was simplifycation and securisation of dynamic SQL. I didn't thing about execution plans. I am speaking on plpgsql's lectures about risks related to dynamic SQL (mainly security risks). Prepared statements are safe, but has some gotcha http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ , so there are some situations, where prepared statements are suboptimal. In plpgsql we have to use dynamic SQL
DECLAREWhen we use USING clause, then dynamic SQL are secure (and readable too) . Every evalaution generate new execution plan with knowleage actual params. It's disadvantage (it's need some time - so don't use it in cycles), and advantage (generated plan will be propably better - not allways (sometimes we must play statistic alchemy game)).
a integer;
r integer;
BEGIN
-- prepared statement
SELECT INTO r * FROM test WHERE c = a;
-- classic dynamic SQL witg risk of SQL injection
EXECUTE 'SELECT * FROM test WHERE c = ' || a INTO r;
-- dynamic SQL with USING (secure and with well execution plan)
EXECUTE 'SELECT * FROM test WHERE c = $1' INTO r USING a;
I unlike SELECT composition. It's bad style:
CREATE OR REPLACE FUNCTION get_names(age integer, class integer)With USING clause I should to write:
RETURNS SETOF persons AS $$
DECLARE sql varchar = 'SELECT * FROM persons ';
BEGIN
IF age IS NOT NULL THEN
sql := sql || 'WHERE age = ' || age;
END IF;
IF class IS NOT NULL THEN
IF strpos(sql, 'WHERE') <> 0 THEN
sql := sql || ' AND ';
ELSE
sql := sql || 'WHERE ';
END IF;
sql := sql || ' class = ' || class;
END IF;
RETURN QUERY EXECUTE sql;
END $$ LANGUAGE plpgsql;
..Optimaliser do predicat's reduction self.
BEGIN
RETURN QUERY EXECUTE
'SELECT * FROM persons
WHERE ($1 IS NULL or age = $1)
AND ($2 IS NULL or class = $2)
USING age, class;
END; $$ LANGUAGE plpgsql;
Tom Lane demonstarated other example of EXECUTE USING:
create or replace function foo() returns trigger as $$This code shows values of all field of NEW record in trigger's body. It wasn't possible before EXECUTE USING in plpgsql.
declare
t text;
begin
for i in 1 .. tg_argv[0] loop
execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)'
into t using new;
raise notice '% = %', tg_argv[i], t;
end loop;
return new;
end $$ language plpgsql;
Any EXECUTE in plpgsql is door to you system for SQL injection. With EXECUTE USING your procedures can be 100% safe.
-- very unsafe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || _tablename || ' WHERE x=''' || _parameter || '''' INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;
-- safe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || quote_ident(_tablename) || ' WHERE x='
|| quote_literal(_parameter) || INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;
-- bullet proof function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || quote_ident(_tablename)::regclass || ' WHERE x=$1'
USING _parameter || INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;
SELECT composition can be necessary when the filter predicate requires additional joins which may not be necessary if the corresponding parameter is missing. I don't think the optimizer is smart enough to perform join elimination in these cases and sometimes, leaving the unnecessary joins may return incorrect or duplicate results or may simply be inefficient.
ReplyDeleteJust - test it.
ReplyDelete