Обсуждение: 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