Re: Non-trivial rewriting sql query - Mailing list pgsql-hackers

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
attributesare 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
thatfar.
 
   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,
invocationfrom   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;
CLOSEc1;       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@Yahoo.com #



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