[odb-users] native query with CTE

[Lisa Fiedler]

Hello,

I was trying to pose the following native query

db->query<queryAbstractNodes_view>(" WITH nodeTable AS( " 
                   "SELECT nodes.node AS node , ROW_NUMBER() OVER (ORDER 
BY node) AS rowNumber "                   "FROM "                   
"(SELECT left(\"edge\"," + to_string(k) + ") AS node "                   
"FROM extracted.\"edge\" "                   "INTERSECT " 
                   "SELECT right(\"edge\"," + to_string(k) + ") " 
                   "FROM extracted.\"edge\") AS nodes) " 
                   "SELECT n1.rowNumber AS node1 , n2.rowNumber AS node2 
"                   "FROM "                   "nodeTable n1 JOIN 
extracted.\"edge\" ON (n1.node = left(\"edge\"," + to_string(k) + ")) " 
                   "JOIN nodeTable n2 ON (n2.node = right(\"edge\"," + 
to_string(k) + "))");

where k is an unsigned integer and the view is defined as

#pragma db view struct queryAbstractNodes_view{     #pragma db 
type("bigint")     countType node1;     #pragma db type("bigint")     
countType node2; };

However odb prepends my query string with "WHERE", such that an error is 
raised:

42601: ERROR: syntax error at or near "WHERE"

LINE 1: WHERE WITH nodeTable AS( SELECT nodes.node AS node , ROW_NU...

^

How can I stop odb from doing this? The query works fine within psql. (I 
am using postgresql).

Thanks a lot.