Skip site navigation (1) Skip section navigation (2)

Re: Non-trivial rewriting sql query

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
Cc: JanWieck(at)Yahoo(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Non-trivial rewriting sql query
Date: 2001-06-27 16:07:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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 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 '
            cur   ALIAS FOR $1;
            t_qry ALIAS FOR $2;
            t_val ALIAS FOR $3;
            i_val ALIAS FOR $4;
            t_qry := t_qry || '' ('' || ourfunc(t_val, i_val) || '')'';
            OPEN cur FOR EXECUTE t_qry;
            RETURN cur;
        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:

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

    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?



# 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 address at

In response to

pgsql-hackers by date

Next:From: Jan WieckDate: 2001-06-27 16:14:54
Subject: Re: functions returning records
Previous:From: Tom LaneDate: 2001-06-27 15:40:12
Subject: Re: Re: 7.2 items

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group