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


Wed, 27 Jun 2001 12:17:26 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Non-trivial rewriting sql query 
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
> 
> 


Wed, 27 Jun 2001 06:42:14 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
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@Yahoo.com #



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


Wed, 27 Jun 2001 12:07:47 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Non-trivial rewriting sql query