WITH RECURSIVE ... simplified syntax?

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)
FROM employee
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

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

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

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

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

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!