Non-trivial rewriting sql query

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;

Can you give us a real life example ? For me this is too abstract to
understand.

Problem with the rewriter is, that it currently has no access to the
where restriction, and can thus only add restrictions without knowledge
of the where clause at hand. Of course you would also need to create
a view and replace the standard "on select" rule, and do your selects
on the view (unless the rewriter is extended to be invoked by a certain
where clause (here a=2) and the rewritten query does not contain this
clause).

Andreas

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:

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