Feature #858

Sql: Adopt With

Added by Miroslav Fidler about 7 years ago. Updated about 7 years ago.

Status:ApprovedStart date:09/13/2014
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:-Spent time:-
Target version:-

History

#1 Updated by Miroslav Fidler about 7 years ago

/* WITH query (from PostgreSQL manual)
   http://www.postgresql.org/docs/8.4/static/queries-with.html
   Should work also in other SQL */
class SqlWith {
    String prefix;
    String table;
    String columns;
public:
    class SqlWithAs {
        String text;
        public:
        SqlWithAs(const String& t) : text(t) {}
        SqlWith With(SqlId table) const { return SqlWith(table, text + ", "); }
        SqlStatement operator()(const SqlSelect& s) { return SqlStatement("with " + text + " " + SqlStatement(s).GetText()); }
    };
    SqlWith(SqlId table, String prefix = "") : table(table.ToString()), prefix(prefix) {}
    void Column(SqlId col) { if(!IsNull(columns)) columns << ", "; columns << "\t" << col.ToString() << "\t"; }
#define E__SqlId(I) const SqlId& p##I
#define E__Column(I) Column(p##I)
#define E__Braces(I) SqlWith& operator()(__List##I(E__SqlId)) { __List##I(E__Column); return *this;}
    __Expand(E__Braces)
#undef  E__Braces
#undef  E__SqlId
#undef  E__SqlId

    SqlWithAs As(const SqlSelect& s) {
        String c;
        if (!IsNull(columns)) c << "(" << columns << ")";
        return SqlWithAs("\t" + table + "\t" + c + " as (" + SqlStatement(s).GetText() + ")");
    }
};

inline SqlWith With(SqlId table) { return SqlWith(table); }
inline SqlWith WithRecursive(SqlId table) { return SqlWith(table, "recursive "); }


#2 Updated by Miroslav Fidler about 7 years ago

SQL example (that has to be supported):

with RECURSIVE tn as (
select id, name, parent_id from treenode where parent_id = -1
union ALL
select treenode.id, treenode.name, treenode.parent_id from treenode, tn where treenode.PARENT_id = tn.ID
),
tng as (select parent_id, (select name from treenode where id = tn.parent_id), count(*) from tn group by parent_id)
select * from tng;

#3 Updated by Miroslav Fidler about 7 years ago

  • Status changed from New to Approved

Also available in: Atom PDF