Обсуждение: Combining several CTEs with a recursive CTE

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

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

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

Hi All,

I have a requirement where my application tries to enter the duplicate row in table using batchexceute code. And batch
exceptionis thrown we checked error code and skip it but after this exception all my next update/insert gets error out
withfollowing 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
isset warning is generated instead of Exception so the other insert/update can proceed ahead. 

Hi<br /><br />There is no IGNORE_DUP_KEY equivalent in PostgreSQL.<br /><br />If you are 100% sure that you want to
ignoreunique key violations, you can wrap your INSERT code in PL/PgSQL block and handle the exception yourself.<br
/><br/>I mean:<br /><br />DO $$<br />BEGIN<br /> INSERT INTO foo (bar,baz) SELECT 42, 666;<br /> EXCEPTION WHEN
unique_violationTHEN RAISE NOTICE 'row skipped';<br />END;<br />$$ <br /><br /><br />BTW - which version of PostgreSQL
areyou using?<br /><br />Filip<br /><br /><br /><br /><div class="gmail_quote">2011/9/21 Amar Dhole <span
dir="ltr"><<ahref="mailto:adhole@tibco.com">adhole@tibco.com</a>></span><br /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> Hi All,<br /><br /> I have a requirement where
myapplication tries to enter the duplicate row in table using batchexceute code. And batch exception is thrown we
checkederror code and skip it but after this exception all my next update/insert gets error out with following
exception<br/><br /> "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
endof transaction block.:<br /><br /><br />  Is there any way to proceed ahead like in sql server we have options while
creatingtable IGNORE_DUP_KEY = ON if this is set warning is generated instead of Exception so the other insert/update
canproceed ahead.<br /><font color="#888888"><br /><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>