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)

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;