From: | Alex Pilosov <alex(at)pilosoft(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 10:42:14 |
Message-ID: | Pine.BSO.4.10.10106270632570.7004-100000@spider.pilosoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Pilosov | 2001-06-27 10:45:58 | Re: AW: AW: functions returning records |
Previous Message | Alex Pilosov | 2001-06-27 10:31:58 | Re: Re: 7.2 items |