Re: Non-trivial rewriting sql query
От | Jan Wieck |
---|---|
Тема | Re: Non-trivial rewriting sql query |
Дата | |
Msg-id | 200106271607.f5RG7lR18604@jupiter.us.greatbridge.com обсуждение исходный текст |
Ответ на | Non-trivial rewriting sql query (Oleg Bartunov <oleg@sai.msu.su>) |
Список | 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 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
В списке pgsql-hackers по дате отправления: