Re: Non-trivial rewriting sql query

Oleg Bartunov wrote:
> Jan,
>
> we're thinking about possibility to integrate our full-text search
> into postgres. There are several problems we should thinking about
> but for now we have a question about rewrite system.
>
> Is't possible to rewrite SQL query and execute it. Currently we build
> sql query outside of postgres using perl.
>
> Let's consider some simple example:
>
> create table tst ( a int4, b int4, c int4);
>
> select * from tst where a=2 and c=0;
>
> we need something like:
>
> select * from tst where str and c=0;
>
> where str is a string resulting by call ourfunc(table.a, 2)
> and looks like 'b=2*2 or b=(2-1)'
>
> i.e. instead of original select we need to execute rewritten select
>
> select * from tst where (b=2*2 or b=(2-1)) and c=0;
>
> in other words we need to know is't possible to recognise
> (operator, field,table) and rewrite part of sql by
> result of calling of ourfunc().
>
> We're not sure if it's a question of rewrite system though.
>
> Any pointers where to go would be very nice.

The problem I see is that this is not the way how the
rewriter works. The rewriter works on querytree structures,
after ALL parsing is done (the one for the rewriting rules
long time ago). Inside of a querytree, the attributes are Var
nodes, pointing to a rangetable entry by index and an
attribute number in that rangetable. Creating additional
qualification expressions could be possible, but I doubt you
really want to go that far.

In the current v7.2 development tree, there is support for
reference cursors in PL/pgSQL. And this support integrates
dynamic queries as well, so you could do it as:

CREATE FUNCTION myfunc(refcursor, text, text, integer)
RETURNS refcursor AS '
DECLARE
cur ALIAS FOR $1;
t_qry ALIAS FOR $2;
t_val ALIAS FOR $3;
i_val ALIAS FOR $4;
BEGIN
t_qry := t_qry || '' ('' || ourfunc(t_val, i_val) || '')'';
OPEN cur FOR EXECUTE t_qry;
RETURN cur;
END;'
LANGUAGE 'plpgsql';

I think at least that's the syntax - did't check so if you
have problems with it, let me know. Anyway, invocation from
the application level then would look like this:

BEGIN;
SELECT myfunc('c1', 'select * from tst where c = 0 and', table.a, 2);
FETCH ALL IN c1;
CLOSE c1;
COMMIT;

You could as well invoke this function inside of another
function, storing it's return value in a refcursor variable
and do fetches inside of the caller.

Would that help?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com