Wednesday, June 18, 2008

recursive queries only with SQL procedures

I tested possibility of recursive queries only with SQL language (and SQL procedures)
postgres=# select * from rec_src;
id | parent | v
----+--------+--------
2 | 1 | rodic
3 | 1 | rodic
4 | 2 | dite
5 | 2 | dite
6 | 3 | dite
7 | 6 | vnouce
1 | 0 | root
(7 rows)
It's ugly, but it works :)
create or replace function empty2null(anyarray)
returns anyarray as $$
select case
when array_upper($1,1) is null
then null
else $1 end;
$$ language sql strict immutable;

create or replace function search_path(int)
returns varchar as $$
select case
when $1 is null
then ''
else search_path((select parent
from rec_src
where id = $1)) || '.'|| $1 end;
$$ language sql;

create or replace function ls(int[], int,
out id int, out parent int, out path varchar, out v varchar )
returns setof record as $$
select id, parent, search_path(id), repeat(' ',$2)|| v
from rec_src
where parent = any($1)
union all
select id, parent, path, v from ls(empty2null(array(select id
from rec_src
where parent = any($1))),
$2 + 1);
$$ language sql volatile strict;
postgres=# select * from ls(array[0], 0) order by path;
id | parent | path | v
----+--------+------------+--------------
1 | 0 | .0.1 | root
2 | 1 | .0.1.2 | rodic
4 | 2 | .0.1.2.4 | dite
5 | 2 | .0.1.2.5 | dite
3 | 1 | .0.1.3 | rodic
6 | 3 | .0.1.3.6 | dite
7 | 6 | .0.1.3.6.7 | vnouce
(7 rows)

Tuesday, June 3, 2008

EXECUTE USING feature in PostgreSQL 8.4

Hello,

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
DECLARE
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;
When 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)).

I unlike SELECT composition. It's bad style:
CREATE OR REPLACE FUNCTION get_names(age integer, class integer)
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;
With USING clause I should to write:
..
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;
Optimaliser do predicat's reduction self.

Tom Lane demonstarated other example of EXECUTE USING:
create or replace function foo() returns trigger as $$
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;
This code shows values of all field of NEW record in trigger's body. It wasn't possible before EXECUTE USING in 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;