[SQL] Combining several CTEs with a recursive CTE

2011-09-20 Thread Thomas Kellerer 

Hello all,

this is more a "just curious" question, rather than a real world problem.

We can combine several CTEs into a single select using something like this:

WITH cte_1 as (
   select 
),
cte_2 as (
   select ...
   where id (select some_col from cte_1)
)
select *
from cte_2;


But this does not seem to work when a recursive CTE is involved


WITH cte_1 as (
   select 
),
recursive cte_2 as (
   select ...
   where id (select some_col from cte_1)

   union all

   select ...
)
select *
from cte_2;

This throws an error: syntax error at or near "cte_2"

I'm just wondering if this is intended behavioury, simply not (yet) implemented 
or even invalid according to the standard? I didn't find any reference that 
it's not allowed in the manual.

Regards
Thomas



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Combining several CTEs with a recursive CTE

2011-09-20 Thread David Johnston 
On Sep 20, 2011, at 5:58, Thomas Kellerer  wrote:

> 
> I'm just wondering if this is intended behavioury, simply not (yet) 
> implemented or even invalid according to the standard? I didn't find any 
> reference that it's not allowed in the manual.
> 
> Regards
> Thomas
> 
> 

Try sticking the recursive keyword after the "with" if any of the following 
CTEs are recursive.  

WITH RECURSIVE 
normal1 AS ()
,recursine1 AS ()
,normal2 AS ()
,recursine2 AS ()
SELECT ...

David J.

Re: [SQL] Combining several CTEs with a recursive CTE

2011-09-20 Thread Thomas Kellerer 

David Johnston, 20.09.2011 16:15:

I'm just wondering if this is intended behavioury, simply not (yet)
implemented or even invalid according to the standard? I didn't
find any reference that it's not allowed in the manual.

Regards Thomas



Try sticking the recursive keyword after the "with" if any of the
following CTEs are recursive.

WITH RECURSIVE normal 1 AS () ,recursine1 AS () ,normal2 AS ()
,recursine2 AS () SELECT ...

David J.



Ah! That does the trick.

Thanks
Thomas



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] handling duplicate row exception

2011-09-20 Thread Amar Dhole 
Hi All,

I have a requirement where my application tries to enter the duplicate row in 
table using batchexceute code. And batch exception is thrown we checked error 
code and skip it but after this exception all my next update/insert gets error 
out with following exception

"Database error. SQL state 25P02. Database specific error code (if any) was 0. 
Database error message (if any) was: org.postgresql.util.PSQLException: ERROR: 
current transaction is aborted, commands ignored until end of transaction 
block.:


 Is there any way to proceed ahead like in sql server we have options while 
creating table IGNORE_DUP_KEY = ON if this is set warning is generated instead 
of Exception so the other insert/update can proceed ahead.



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql