WITH RECURSIVE throws `Unexpected comma token: ","` error

[BrandiATMuhkuh]

It seems like when using the RECURSIVE function, other WITH statements are not allowed any longer. This issue does not happen when I only use WITH statements which have no RECURSIVE in them

You can use https://npm.runkit.com/pgsql-ast-parser to test the no-working codes.

NOT WORKING: Recursion before other WITH statements

var pgsqlAstParser = require("pgsql-ast-parser");

const tables = new Set();
let joins = 0;
const visitor = pgsqlAstParser.astVisitor(map => ({
 
    // implement here AST parts you want to hook
 
    tableRef: t => tables.add(t.name),
    join: t => {
        joins++;
        // call the default implementation of 'join'
        // this will ensure that the subtree is also traversed.
        map.super().join(t);
    }
}))
 
// start traversing a statement
visitor.statement(pgsqlAstParser.parseFirst(`
WITH 
RECURSIVE t(n) AS (
    VALUES (1)
    
    UNION ALL
        SELECT n+1 FROM t WHERE n < 100
),

test as (
    select * from abc
),

test2 as (
    select * from abc
)


SELECT sum(n) FROM t

`));

NOT WORKING: Recursion after other WITH statements

var pgsqlAstParser = require("pgsql-ast-parser");

const tables = new Set();
let joins = 0;
const visitor = pgsqlAstParser.astVisitor(map => ({
 
    // implement here AST parts you want to hook
 
    tableRef: t => tables.add(t.name),
    join: t => {
        joins++;
        // call the default implementation of 'join'
        // this will ensure that the subtree is also traversed.
        map.super().join(t);
    }
}))
 
// start traversing a statement
visitor.statement(pgsqlAstParser.parseFirst(`
WITH 


test as (
    select * from abc
),

test2 as (
    select * from abc
),

RECURSIVE t(n) AS (
    VALUES (1)
    
    UNION ALL
        SELECT n+1 FROM t WHERE n < 100
)


SELECT sum(n) FROM t

`));

WORKING: Recursion without any extra WITH statements

var pgsqlAstParser = require("pgsql-ast-parser");

const tables = new Set();
let joins = 0;
const visitor = pgsqlAstParser.astVisitor(map => ({
 
    // implement here AST parts you want to hook
 
    tableRef: t => tables.add(t.name),
    join: t => {
        joins++;
        // call the default implementation of 'join'
        // this will ensure that the subtree is also traversed.
        map.super().join(t);
    }
}))
 
// start traversing a statement
visitor.statement(pgsqlAstParser.parseFirst(`
WITH 
RECURSIVE t(n) AS (
    VALUES (1)
    
    UNION ALL
        SELECT n+1 FROM t WHERE n < 100
)


SELECT sum(n) FROM t

`));

WORKING: No recursion with multiple WITH statements

var pgsqlAstParser = require("pgsql-ast-parser");

const tables = new Set();
let joins = 0;
const visitor = pgsqlAstParser.astVisitor(map => ({
 
    // implement here AST parts you want to hook
 
    tableRef: t => tables.add(t.name),
    join: t => {
        joins++;
        // call the default implementation of 'join'
        // this will ensure that the subtree is also traversed.
        map.super().join(t);
    }
}))
 
// start traversing a statement
visitor.statement(pgsqlAstParser.parseFirst(`
WITH 


test as (
    select * from abc
),

test2 as (
    select * from abc
)


SELECT * from test2

`));