Thread: WITH RECURSIVE ... simplified syntax?
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
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
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 >
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!