Thread: Non-trivial rewriting sql query

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.
Regards,
    Oleg

I believe (while I'm not an expert on this) that rewrite system cannot
cope with dynamically-rewritten queries. (i.e. the rewrite rules where a
function must be called to obtain the result of rewrite rule).

A better possibility for you is to return a refcursor, and use on client
side "FETCH ALL from rc", if possible.

I.E, client would do:
select setup_query('c=0', 'rc');
fetch all from rc;

create function setup_query(text, refcursor) returns int4 as '
declare
qry alias for $1;
cur alias for $2;
begin
execute ''declare '' || cur || '' cursor for select ... '' || qry ||
ourfunc(....)




-alex
On Wed, 27 Jun 2001, 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.
> 
>     Regards,
> 
>         Oleg
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 
> 

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