Thread: WITH RECURSIVE ... simplified syntax?

WITH RECURSIVE ... simplified syntax?

From
Josh Berkus
Date:
All,

I was discussing WITH RECURSIVE the other day, and realized that one thing 
which we're not getting with this patch is a simplest-case simple syntax 
which 75% of users are looking for.  You know, the ones with simple 
proximity trees who just want to find all children of one parent.

Would it be a worth it for us to implement a non-standard simple syntax 
sugar on top of WITH RECURSIVE?  Or, at least, something like 
CONNECT_BY()?

-- 
--Josh

Josh Berkus
PostgreSQL
San Francisco


Re: WITH RECURSIVE ... simplified syntax?

From
"Dickson S. Guedes"
Date:
Josh Berkus escreveu:
> All,
>
> I was discussing WITH RECURSIVE the other day, and realized that one thing 
> which we're not getting with this patch is a simplest-case simple syntax 
> which 75% of users are looking for.  You know, the ones with simple 
> proximity trees who just want to find all children of one parent.
>
> Would it be a worth it for us to implement a non-standard simple syntax 
> sugar on top of WITH RECURSIVE?  Or, at least, something like 
> CONNECT_BY()

Yes Josh,

I was discussing WITH RECURSIVE with some students that I'm teaching and 
they ask me exactly this:

"Why not use a syntax like...

SELECT level, lpad(' ', level*4) || last_name as last_name
FROM employee
START WITH employee_id = 10
CONNECT BY PRIOR employee_id = manager_id;

... that is rewrite (or aliased) in:

WITH RECURSIVE employee_rec(level, employee_id, last_name) AS           (SELECT 1, employee_id, last_name)
FROMemployee             WHERE employee_id = 10           UNION ALL           SELECT employee_rec.level + 1,
emp.employee_id,emp.last_name              FROM employee as emp, employee_rec             WHERE
employee_rec.employee_id= emp.manager_id)
 
SELECT level, lpad(' ', level*4) || last_name FROM employee_rec;" ?


In my opnion, it will be more simple to understand too.


-- 

[]s
Dickson S. Guedes
Administrador de Banco de Dados
Projeto Colmeia -  Florianópolis, SC
(48) 3322-1185, ramal: 26



Re: WITH RECURSIVE ... simplified syntax?

From
"Pavel Stehule"
Date:
Hello

2008/10/9 Josh Berkus <josh@agliodbs.com>:
> All,
>
> I was discussing WITH RECURSIVE the other day, and realized that one thing
> which we're not getting with this patch is a simplest-case simple syntax
> which 75% of users are looking for.  You know, the ones with simple
> proximity trees who just want to find all children of one parent.
>

I thing so it's bad understanding of new syntax. It's really power and
easy, but it minimally needs look to manual. I am against to add some
non standard syntax, when we have standard.

regards
Pavel Stehule

p.s. CONNECT by should help to people with migration of older
application from DB2 or Oracle

> Would it be a worth it for us to implement a non-standard simple syntax
> sugar on top of WITH RECURSIVE?  Or, at least, something like
> CONNECT_BY()?
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL
> San Francisco
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: WITH RECURSIVE ... simplified syntax?

From
David Fetter
Date:
On Wed, Oct 08, 2008 at 04:11:45PM -0700, Josh Berkus wrote:
> All,
> 
> I was discussing WITH RECURSIVE the other day, and realized that one thing 
> which we're not getting with this patch is a simplest-case simple syntax 
> which 75% of users are looking for.  You know, the ones with simple 
> proximity trees who just want to find all children of one parent.
> 
> Would it be a worth it for us to implement a non-standard simple syntax 
> sugar on top of WITH RECURSIVE?  Or, at least, something like 
> CONNECT_BY()?

No.

The simple syntax really is simple.  For an adjacency list consisting
of (id, parent_id) pairs, you do:

WITH RECURSIVE t(id, tree_path) AS (
/* Initial Condition */   SELECT a1.id, ARRAY[a1.id]   FROM adjacency a1   WHERE a1.id = 1

/* De-Cyclifier */
UNION

/* Recursion Step */   SELECT a2.id, t.tree_path || a2.id   FROM       adjacency a2   JOIN       t       ON
(a2.parent_id= t.id)
 
)
SELECT * FROM t ORDER BY path;

While it looks a tad wordy at first, it's really simple.  The WITH
part of the query is in two parts.  The first is the initial condition
a.k.a. the root node of the tree.  The second is the recursion step
which gets all the way to the branches.  In between is UNION, which as
of Tom's recent patch now Does The Right Thing(TM) as far as
eliminating cycles.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: WITH RECURSIVE ... simplified syntax?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Would it be a worth it for us to implement a non-standard simple syntax 
> sugar on top of WITH RECURSIVE?  Or, at least, something like 
> CONNECT_BY()?

The Oracle syntax only *looks* simple.  When you start to study it
you realize that it's a horrid, messy kluge.
        regards, tom lane


Re: WITH RECURSIVE ... simplified syntax?

From
Josh Berkus
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> Would it be a worth it for us to implement a non-standard simple syntax 
>> sugar on top of WITH RECURSIVE?  Or, at least, something like 
>> CONNECT_BY()?
> 
> The Oracle syntax only *looks* simple.  When you start to study it
> you realize that it's a horrid, messy kluge.

Yeah. I was actually thinking more of something like:

<table_identifier> RECURSIVE JOIN ON <column_expression> <operator> 
<column_expression>

... which would be a pretty good simplest-case syntax.

When I mentioned connect_by() I was referring to Joe's function, not 
Oracle's syntax, which I think is awful.

--Josh



Re: WITH RECURSIVE ... simplified syntax?

From
Gregory Stark
Date:
Josh Berkus <josh@agliodbs.com> writes:

> Tom Lane wrote:
>> Josh Berkus <josh@agliodbs.com> writes:
>>> Would it be a worth it for us to implement a non-standard simple syntax
>>> sugar on top of WITH RECURSIVE?  Or, at least, something like CONNECT_BY()?
>>
>> The Oracle syntax only *looks* simple.  When you start to study it
>> you realize that it's a horrid, messy kluge.
>
> Yeah. I was actually thinking more of something like:
>
> <table_identifier> RECURSIVE JOIN ON <column_expression> <operator>
> <column_expression>
>
> ... which would be a pretty good simplest-case syntax.
>
> When I mentioned connect_by() I was referring to Joe's function, not Oracle's
> syntax, which I think is awful.

Sounds good*... until you start thinking about the details. Which is precisely
where Oracle's syntax breaks down too. In the above syntax where do you
specify the base case? Where do you specify any restrictions which stop
infinite recursion? How do you get the recursion depth for a record? These
aren't optional bells and whistles, the feature is unusable without them,
basically every recursive query needs to use at least the first two and
usually all three.

* (actually it's awful but I think what you really meant to say was <table_ref> RECURSIVE JOIN <join_qual>)

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!