Re: Recursive queries?

hi again,

at last i found patch for WITH and make it working.
it's resides in attach.

what it does:
WITH a AS (SELECT * FROM t) SELECT * FROM a;
WITH a AS (SELECT * FROM t), b as (SELECT * FROM t) SELECT * FROM
a.id=b.id;
WITH a AS (SELECT * FROM t), b AS (SELECT * FROM a) SELECT * FROM b;

where t is real table such as (id int, val varchar(10)).
what it doesn't:
WITH RECURSIVE didn't implemented.
it's unclean, executor part is a braindead a bit and need to be rewriten.
anything other, almost any checks and restrinctions, so on - it's
nothing more than working prototype.

how it works:
single node "With" contains list of all WITH subqueries,

each subquery described by WithSubPlan which stores subquery itself,
subquery rtable,working and result tublestores, some flags.

each WITH subquery alias represented by WithScan node , it stores
pointer to With node, and index in list of subqueries of this subquery.

when first access (exec,init,end,...) to any WithScan node is made, it
passes to With node, and it process all WITH subqueries and stores
results in result tuplestores per each subquery.
after that all WithScan nodes fetchs data from result tuplestores and
returns it as from table.

to add RECURSIVE support need at parse state add function which will be
determining right order of WITH subqueries executing, and handling of
many passes in executor.

regards,
.evgen

Hans-Jürgen Schönig wrote:

> evgen wrote:
>
>> hello hackers,
>>
>> some time ago i played with PgSQL and have written simpliest working
>> prototype of WITH clause for it.
>> it don't do any checks and performs only simpliest selects, but it
>> works.
>> i can contribute it and develop it further to production state.
>>
>> regards,
>> .evgen
>>
>
>
> I suggest sending your patch to this list so that people can have a
> look at it.
>
> Regards,
>
> Hans
>
>