1. Overview

The parser examples have the sql source on the left, and the ast that the parser produces the right, the annotations have been replaced with a placeholder 'A' to make the output a bit more readable.

2. lexing

'string'
[SqlString "'" "'" "string"]
E'string\n'
[SqlString "E'" "'" "string\\n"]
E'bsquoteend\''
[SqlString "E'" "'" "bsquoteend\\'"]
E'bsquote\'xx'
[SqlString "E'" "'" "bsquote\\'xx"]
E'quoteend'''
[SqlString "E'" "'" "quoteend''"]
E'quote''x'
[SqlString "E'" "'" "quote''x"]
'normal '' quote'
[SqlString "'" "'" "normal '' quote"]
'normalendquote '''
[SqlString "'" "'" "normalendquote ''"]
$$dollar quoting$$
[SqlString "$$" "$$" "dollar quoting"]
$x$dollar $$ quoting$x$
[SqlString "$x$" "$x$" "dollar $$ quoting"]
test
[Identifier Nothing "test"]
_test
[Identifier Nothing "_test"]
"test test"
[Identifier (Just ("\"", "\"")) "test test"]
test123
[Identifier Nothing "test123"]
[test "]
[Identifier (Just ("[", "]")) "test \""]
@test
[PrefixedVariable '@' "test"]
#test
[PrefixedVariable '#' "test"]
:test
[PrefixedVariable ':' "test"]
+
[Symbol "+"]
*
[Symbol "*"]
10
[SqlNumber "10"]
.1
[SqlNumber ".1"]
5e3
[SqlNumber "5e3"]
5e+3
[SqlNumber "5e+3"]
5e-3
[SqlNumber "5e-3"]
10.2
[SqlNumber "10.2"]
10.2e7
[SqlNumber "10.2e7"]
[Whitespace " "]
[Whitespace "  "]
[Whitespace "\n"]
[Whitespace "\t"]
a.b
[Identifier Nothing "a", Symbol ".", Identifier Nothing "b"]
$1
[PositionalArg 1]
-- this is a comment
[LineComment "-- this is a comment\n"]
-- this is a comment
[LineComment "-- this is a comment"]
/* block
comment */
[BlockComment "/* block\ncomment */"]
/* nested /*block*/ comment */
[BlockComment "/* nested /*block*/ comment */"]
$c(splice)
[Splice 'c' "splice"]
1 .. 2
[SqlNumber "1", Whitespace " ", Symbol "..", Whitespace " ",
 SqlNumber "2"]
1..2
[SqlNumber "1", Symbol "..", SqlNumber "2"]

3. parse expressions

3.1. numbers

42
NumberLit (A) "42"
3.5
NumberLit (A) "3.5"
4.
NumberLit (A) "4."
.001
NumberLit (A) ".001"
5e2
NumberLit (A) "5e2"
1.925e-3
NumberLit (A) "1.925e-3"

3.2. basic expressions

1
NumberLit (A) "1"
-1
PrefixOp (A) (Name (A) [Nmc "-"]) (NumberLit (A) "1")
1.1
NumberLit (A) "1.1"
-1.1
PrefixOp (A) (Name (A) [Nmc "-"]) (NumberLit (A) "1.1")
 1 + 1
BinaryOp (A) (Name (A) [Nmc "+"]) (NumberLit (A) "1")
  (NumberLit (A) "1")
1+1+1
BinaryOp (A) (Name (A) [Nmc "+"])
  (BinaryOp (A) (Name (A) [Nmc "+"]) (NumberLit (A) "1")
     (NumberLit (A) "1"))
  (NumberLit (A) "1")

3.3. parens

(1)
Parens (A) (NumberLit (A) "1")
row ()
SpecialOp (A) (Name (A) [Nmc "rowctor"]) []
row (1)
SpecialOp (A) (Name (A) [Nmc "rowctor"]) [NumberLit (A) "1"]
row (1,2)
SpecialOp (A) (Name (A) [Nmc "rowctor"])
  [NumberLit (A) "1", NumberLit (A) "2"]
(1,2)
SpecialOp (A) (Name (A) [Nmc "rowctor"])
  [NumberLit (A) "1", NumberLit (A) "2"]

3.4. more basic expressions

'test'
StringLit (A) "test"
''
StringLit (A) ""
hello
Identifier (A) (Name (A) [Nmc "hello"])
helloTest
Identifier (A) (Name (A) [Nmc "helloTest"])
hello_test
Identifier (A) (Name (A) [Nmc "hello_test"])
"this is an identifier"
Identifier (A) (Name (A) [QNmc "this is an identifier"])
hello1234
Identifier (A) (Name (A) [Nmc "hello1234"])
true
BooleanLit (A) True
false
BooleanLit (A) False
null
NullLit (A)

3.5. array ctor and selector

array[1,2]
SpecialOp (A) (Name (A) [Nmc "arrayctor"])
  [NumberLit (A) "1", NumberLit (A) "2"]
a[1]
SpecialOp (A) (Name (A) [Nmc "arraysub"])
  [Identifier (A) (Name (A) [Nmc "a"]), NumberLit (A) "1"]

3.6. simple operators

1 + tst1
BinaryOp (A) (Name (A) [Nmc "+"]) (NumberLit (A) "1")
  (Identifier (A) (Name (A) [Nmc "tst1"]))
tst1 + 1
BinaryOp (A) (Name (A) [Nmc "+"])
  (Identifier (A) (Name (A) [Nmc "tst1"]))
  (NumberLit (A) "1")
tst + tst1
BinaryOp (A) (Name (A) [Nmc "+"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
'a' || 'b'
BinaryOp (A) (Name (A) [Nmc "||"]) (StringLit (A) "a")
  (StringLit (A) "b")
tst | tst1
BinaryOp (A) (Name (A) [Nmc "|"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
tst & tst1
BinaryOp (A) (Name (A) [Nmc "&"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
tst # tst1
BinaryOp (A) (Name (A) [Nmc "#"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
tst << tst1
BinaryOp (A) (Name (A) [Nmc "<<"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
tst >> tst1
BinaryOp (A) (Name (A) [Nmc ">>"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
  (Identifier (A) (Name (A) [Nmc "tst1"]))
~tst
PrefixOp (A) (Name (A) [Nmc "~"])
  (Identifier (A) (Name (A) [Nmc "tst"]))
~tst + tst1
PrefixOp (A) (Name (A) [Nmc "~"])
  (BinaryOp (A) (Name (A) [Nmc "+"])
     (Identifier (A) (Name (A) [Nmc "tst"]))
     (Identifier (A) (Name (A) [Nmc "tst1"])))
2 + 1 << 3
BinaryOp (A) (Name (A) [Nmc "<<"])
  (BinaryOp (A) (Name (A) [Nmc "+"]) (NumberLit (A) "2")
     (NumberLit (A) "1"))
  (NumberLit (A) "3")
'stuff'::text
Cast (A) (StringLit (A) "stuff")
  (SimpleTypeName (A) (Name (A) [Nmc "text"]))
245::float(24)
Cast (A) (NumberLit (A) "245")
  (PrecTypeName (A) (Name (A) [Nmc "float"]) 24)
245.1::numeric(5,3)
Cast (A) (NumberLit (A) "245.1")
  (Prec2TypeName (A) (Name (A) [Nmc "numeric"]) 5 3)
245::double precision
Cast (A) (NumberLit (A) "245")
  (SimpleTypeName (A) (Name (A) [Nmc "double precision"]))
'test'::character varying(6)
Cast (A) (StringLit (A) "test")
  (PrecTypeName (A) (Name (A) [Nmc "character varying"]) 6)
date '1998-12-01'
TypedStringLit (A) (SimpleTypeName (A) (Name (A) [Nmc "date"]))
  "1998-12-01"
interval '63' day
Interval (A) "63" IntervalDay Nothing
interval '63' day (3)
Interval (A) "63" IntervalDay (Just 3)
interval '63' minute
Interval (A) "63" IntervalMinute Nothing
EXTRACT(year from a)
Extract (A) ExtractYear (Identifier (A) (Name (A) [Nmc "a"]))
a between 1 and 3
SpecialOp (A) (Name (A) [Nmc "between"])
  [Identifier (A) (Name (A) [Nmc "a"]), NumberLit (A) "1",
   NumberLit (A) "3"]
a between 7 - 1 and 7 + 1
SpecialOp (A) (Name (A) [Nmc "between"])
  [Identifier (A) (Name (A) [Nmc "a"]),
   BinaryOp (A) (Name (A) [Nmc "-"]) (NumberLit (A) "7")
     (NumberLit (A) "1"),
   BinaryOp (A) (Name (A) [Nmc "+"]) (NumberLit (A) "7")
     (NumberLit (A) "1")]
cast(a as text)
Cast (A) (Identifier (A) (Name (A) [Nmc "a"]))
  (SimpleTypeName (A) (Name (A) [Nmc "text"]))
@ a
PrefixOp (A) (Name (A) [Nmc "@"])
  (Identifier (A) (Name (A) [Nmc "a"]))
substring(a from 0 for 3)
SpecialOp (A) (Name (A) [Nmc "substring"])
  [Identifier (A) (Name (A) [Nmc "a"]), NumberLit (A) "0",
   NumberLit (A) "3"]
substring(a from 0 for (5 - 3))
SpecialOp (A) (Name (A) [Nmc "substring"])
  [Identifier (A) (Name (A) [Nmc "a"]), NumberLit (A) "0",
   Parens (A)
     (BinaryOp (A) (Name (A) [Nmc "-"]) (NumberLit (A) "5")
        (NumberLit (A) "3"))]
substring(a,b,c)
App (A) (Name (A) [Nmc "substring"])
  [Identifier (A) (Name (A) [Nmc "a"]),
   Identifier (A) (Name (A) [Nmc "b"]),
   Identifier (A) (Name (A) [Nmc "c"])]
a like b
BinaryOp (A) (Name (A) [Nmc "like"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))
a rlike b
BinaryOp (A) (Name (A) [Nmc "rlike"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))
a not like b
BinaryOp (A) (Name (A) [Nmc "notlike"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))
a and b and c and d
BinaryOp (A) (Name (A) [Nmc "and"])
  (BinaryOp (A) (Name (A) [Nmc "and"])
     (BinaryOp (A) (Name (A) [Nmc "and"])
        (Identifier (A) (Name (A) [Nmc "a"]))
        (Identifier (A) (Name (A) [Nmc "b"])))
     (Identifier (A) (Name (A) [Nmc "c"])))
  (Identifier (A) (Name (A) [Nmc "d"]))

3.7. function calls

fn()
App (A) (Name (A) [Nmc "fn"]) []
fn(1)
App (A) (Name (A) [Nmc "fn"]) [NumberLit (A) "1"]
fn('test')
App (A) (Name (A) [Nmc "fn"]) [StringLit (A) "test"]
fn(1,'test')
App (A) (Name (A) [Nmc "fn"])
  [NumberLit (A) "1", StringLit (A) "test"]
fn('test')
App (A) (Name (A) [Nmc "fn"]) [StringLit (A) "test"]
g.f()
App (A) (Name (A) [Nmc "g", Nmc "f"]) []
h.g.f()
App (A) (Name (A) [Nmc "h", Nmc "g", Nmc "f"]) []

3.8. simple whitespace sanity checks

fn (1)
App (A) (Name (A) [Nmc "fn"]) [NumberLit (A) "1"]
fn( 1)
App (A) (Name (A) [Nmc "fn"]) [NumberLit (A) "1"]
fn(1 )
App (A) (Name (A) [Nmc "fn"]) [NumberLit (A) "1"]
fn(1)
App (A) (Name (A) [Nmc "fn"]) [NumberLit (A) "1"]

3.9. null stuff

not null
PrefixOp (A) (Name (A) [Nmc "not"]) (NullLit (A))
a is null
PostfixOp (A) (Name (A) [Nmc "isnull"])
  (Identifier (A) (Name (A) [Nmc "a"]))
a is not null
PostfixOp (A) (Name (A) [Nmc "isnotnull"])
  (Identifier (A) (Name (A) [Nmc "a"]))
not not true
PrefixOp (A) (Name (A) [Nmc "not"])
  (PrefixOp (A) (Name (A) [Nmc "not"]) (BooleanLit (A) True))

3.10. case expressions


           case when a,b then 3
                when c then 4
                else 5
           end
Case (A)
  [([Identifier (A) (Name (A) [Nmc "a"]),
     Identifier (A) (Name (A) [Nmc "b"])],
    NumberLit (A) "3"),
   ([Identifier (A) (Name (A) [Nmc "c"])], NumberLit (A) "4")]
  (Just (NumberLit (A) "5"))
case 1 when 2 then 3 else 4 end
CaseSimple (A) (NumberLit (A) "1")
  [([NumberLit (A) "2"], NumberLit (A) "3")]
  (Just (NumberLit (A) "4"))

3.11. positional args

$1
PositionalArg (A) 1
?
Placeholder (A)
a = ?
BinaryOp (A) (Name (A) [Nmc "="])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Placeholder (A))

3.12. exists

exists (select 1 from a)
Exists (A)
  (Select{ann = A, selDistinct = All,
          selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
          selTref = [Tref (A) (Name (A) [Nmc "a"])], selWhere = Nothing,
          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
          selLimit = Nothing, selOffset = Nothing, selOption = []})

3.13. in variants

t in (1,2)
InPredicate (A) (Identifier (A) (Name (A) [Nmc "t"])) True
  (InList (A) [NumberLit (A) "1", NumberLit (A) "2"])
t not in (1,2)
InPredicate (A) (Identifier (A) (Name (A) [Nmc "t"])) False
  (InList (A) [NumberLit (A) "1", NumberLit (A) "2"])
(t,u) in (1,2)
InPredicate (A)
  (SpecialOp (A) (Name (A) [Nmc "rowctor"])
     [Identifier (A) (Name (A) [Nmc "t"]),
      Identifier (A) (Name (A) [Nmc "u"])])
  True
  (InList (A) [NumberLit (A) "1", NumberLit (A) "2"])
3 = any (array[1,2])
LiftApp (A) (Name (A) [Nmc "="]) LiftAny
  [NumberLit (A) "3",
   SpecialOp (A) (Name (A) [Nmc "arrayctor"])
     [NumberLit (A) "1", NumberLit (A) "2"]]
3 = all (array[1,2,4])
LiftApp (A) (Name (A) [Nmc "="]) LiftAll
  [NumberLit (A) "3",
   SpecialOp (A) (Name (A) [Nmc "arrayctor"])
     [NumberLit (A) "1", NumberLit (A) "2", NumberLit (A) "4"]]

3.14. comparison operators

a < b
BinaryOp (A) (Name (A) [Nmc "<"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))
a <> b
BinaryOp (A) (Name (A) [Nmc "<>"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))
a != b
BinaryOp (A) (Name (A) [Nmc "<>"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))

3.15. string parsing

''
StringLit (A) ""
''''
StringLit (A) "'"
'test'''
StringLit (A) "test'"
'''test'
StringLit (A) "'test"
'te''st'
StringLit (A) "te'st"
$$test$$
StringLit (A) "test"
$$te'st$$
StringLit (A) "te'st"
$st$test$st$
StringLit (A) "test"
$outer$te$$yup$$st$outer$
StringLit (A) "te$$yup$$st"
'spl$$it'
StringLit (A) "spl$$it"

3.16. bracketed things

(p).x
BinaryOp (A) (Name (A) [Nmc "."])
  (Parens (A) (Identifier (A) (Name (A) [Nmc "p"])))
  (Identifier (A) (Name (A) [Nmc "x"]))
(select f(((a).x, y)::z))
ScalarSubQuery (A)
  (Select{ann = A, selDistinct = All,
          selSelectList =
            SelectList (A)
              [SelExp (A)
                 (App (A) (Name (A) [Nmc "f"])
                    [Cast (A)
                       (SpecialOp (A) (Name (A) [Nmc "rowctor"])
                          [BinaryOp (A) (Name (A) [Nmc "."])
                             (Parens (A) (Identifier (A) (Name (A) [Nmc "a"])))
                             (Identifier (A) (Name (A) [Nmc "x"])),
                           Identifier (A) (Name (A) [Nmc "y"])])
                       (SimpleTypeName (A) (Name (A) [Nmc "z"]))])],
          selTref = [], selWhere = Nothing, selGroupBy = [],
          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
          selOffset = Nothing, selOption = []})

3.17. tricky operator parsing

2 <>-1
BinaryOp (A) (Name (A) [Nmc "<>"]) (NumberLit (A) "2")
  (PrefixOp (A) (Name (A) [Nmc "-"]) (NumberLit (A) "1"))
a <-> b
BinaryOp (A) (Name (A) [Nmc "<->"])
  (Identifier (A) (Name (A) [Nmc "a"]))
  (Identifier (A) (Name (A) [Nmc "b"]))

4. misc select statements

select 1;
Select{ann = A, selDistinct = All,
       selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
       selTref = [], selWhere = Nothing, selGroupBy = [],
       selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
       selOffset = Nothing, selOption = []}
select a from t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select distinct a from t;
Select{ann = A, selDistinct = Distinct,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t where b=2;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])],
       selWhere =
         Just
           (BinaryOp (A) (Name (A) [Nmc "="])
              (Identifier (A) (Name (A) [Nmc "b"]))
              (NumberLit (A) "2")),
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t where b=2 and c=3;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])],
       selWhere =
         Just
           (BinaryOp (A) (Name (A) [Nmc "and"])
              (BinaryOp (A) (Name (A) [Nmc "="])
                 (Identifier (A) (Name (A) [Nmc "b"]))
                 (NumberLit (A) "2"))
              (BinaryOp (A) (Name (A) [Nmc "="])
                 (Identifier (A) (Name (A) [Nmc "c"]))
                 (NumberLit (A) "3"))),
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
SELECT T.A::INT FROM TBL AS T;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (Cast (A) (Identifier (A) (Name (A) [Nmc "T", Nmc "A"]))
                 (SimpleTypeName (A) (Name (A) [Nmc "INT"])))],
       selTref =
         [TableAlias (A) (Nmc "T") (Tref (A) (Name (A) [Nmc "TBL"]))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t order by a;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a asc;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a desc;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a,b;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault),
          (Identifier (A) (Name (A) [Nmc "b"]), Asc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a asc,b;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault),
          (Identifier (A) (Name (A) [Nmc "b"]), Asc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a desc,b;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsDefault),
          (Identifier (A) (Name (A) [Nmc "b"]), Asc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a desc,b desc;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsDefault),
          (Identifier (A) (Name (A) [Nmc "b"]), Desc, NullsDefault)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a nulls first;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsFirst)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a nulls last;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsLast)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a asc nulls first;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsFirst)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a asc nulls last;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsLast)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a desc nulls first;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsFirst)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t order by a desc nulls last;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsLast)],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from t limit 1;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Just (NumberLit (A) "1"), selOffset = Nothing,
       selOption = []}
select a from t offset 1;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Just (NumberLit (A) "1"),
       selOption = []}
select a from t order by a limit 1 offset 1;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing,
       selOrderBy =
         [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)],
       selLimit = Just (NumberLit (A) "1"),
       selOffset = Just (NumberLit (A) "1"), selOption = []}
select (p).x, (p).y from pos;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (BinaryOp (A) (Name (A) [Nmc "."])
                 (Parens (A) (Identifier (A) (Name (A) [Nmc "p"])))
                 (Identifier (A) (Name (A) [Nmc "x"]))),
            SelExp (A)
              (BinaryOp (A) (Name (A) [Nmc "."])
                 (Parens (A) (Identifier (A) (Name (A) [Nmc "p"])))
                 (Identifier (A) (Name (A) [Nmc "y"])))],
       selTref = [Tref (A) (Name (A) [Nmc "pos"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select ($1).x, ($1).y from pos;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (BinaryOp (A) (Name (A) [Nmc "."])
                 (Parens (A) (PositionalArg (A) 1))
                 (Identifier (A) (Name (A) [Nmc "x"]))),
            SelExp (A)
              (BinaryOp (A) (Name (A) [Nmc "."])
                 (Parens (A) (PositionalArg (A) 1))
                 (Identifier (A) (Name (A) [Nmc "y"])))],
       selTref = [Tref (A) (Name (A) [Nmc "pos"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select right ('test string',5) from t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (App (A) (Name (A) [Nmc "right"])
                 [StringLit (A) "test string", NumberLit (A) "5"])],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}

5. combineSelects

select a from tbl
except
select a from tbl1;
CombineQueryExpr{ann = A, cqType = Except,
                 cqQe0 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []},
                 cqQe1 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl1"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []}}
select a from tbl where true
except
select a from tbl1 where true;
CombineQueryExpr{ann = A, cqType = Except,
                 cqQe0 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl"])],
                          selWhere = Just (BooleanLit (A) True), selGroupBy = [],
                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                          selOffset = Nothing, selOption = []},
                 cqQe1 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl1"])],
                          selWhere = Just (BooleanLit (A) True), selGroupBy = [],
                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                          selOffset = Nothing, selOption = []}}
select a from tbl
union
select a from tbl1;
CombineQueryExpr{ann = A, cqType = Union,
                 cqQe0 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []},
                 cqQe1 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl1"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []}}
select a from tbl
union all
select a from tbl1;
CombineQueryExpr{ann = A, cqType = UnionAll,
                 cqQe0 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []},
                 cqQe1 =
                   Select{ann = A, selDistinct = All,
                          selSelectList =
                            SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                          selTref = [Tref (A) (Name (A) [Nmc "tbl1"])], selWhere = Nothing,
                          selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                          selLimit = Nothing, selOffset = Nothing, selOption = []}}
(select 1 union select 2) union select 3;
CombineQueryExpr{ann = A, cqType = Union,
                 cqQe0 =
                   CombineQueryExpr{ann = A, cqType = Union,
                                    cqQe0 =
                                      Select{ann = A, selDistinct = All,
                                             selSelectList =
                                               SelectList (A) [SelExp (A) (NumberLit (A) "1")],
                                             selTref = [], selWhere = Nothing, selGroupBy = [],
                                             selHaving = Nothing, selOrderBy = [],
                                             selLimit = Nothing, selOffset = Nothing,
                                             selOption = []},
                                    cqQe1 =
                                      Select{ann = A, selDistinct = All,
                                             selSelectList =
                                               SelectList (A) [SelExp (A) (NumberLit (A) "2")],
                                             selTref = [], selWhere = Nothing, selGroupBy = [],
                                             selHaving = Nothing, selOrderBy = [],
                                             selLimit = Nothing, selOffset = Nothing,
                                             selOption = []}},
                 cqQe1 =
                   Select{ann = A, selDistinct = All,
                          selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "3")],
                          selTref = [], selWhere = Nothing, selGroupBy = [],
                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                          selOffset = Nothing, selOption = []}}
select 1 union (select 2 union select 3);
CombineQueryExpr{ann = A, cqType = Union,
                 cqQe0 =
                   Select{ann = A, selDistinct = All,
                          selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
                          selTref = [], selWhere = Nothing, selGroupBy = [],
                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                          selOffset = Nothing, selOption = []},
                 cqQe1 =
                   CombineQueryExpr{ann = A, cqType = Union,
                                    cqQe0 =
                                      Select{ann = A, selDistinct = All,
                                             selSelectList =
                                               SelectList (A) [SelExp (A) (NumberLit (A) "2")],
                                             selTref = [], selWhere = Nothing, selGroupBy = [],
                                             selHaving = Nothing, selOrderBy = [],
                                             selLimit = Nothing, selOffset = Nothing,
                                             selOption = []},
                                    cqQe1 =
                                      Select{ann = A, selDistinct = All,
                                             selSelectList =
                                               SelectList (A) [SelExp (A) (NumberLit (A) "3")],
                                             selTref = [], selWhere = Nothing, selGroupBy = [],
                                             selHaving = Nothing, selOrderBy = [],
                                             selLimit = Nothing, selOffset = Nothing,
                                             selOption = []}}}

           with a as (select a from tbl),
                b as (select a from tbl1)
                select 1;
WithQueryExpr{ann = A,
              withs =
                [WithQuery (A) (Nmc "a") Nothing
                   (Select{ann = A, selDistinct = All,
                           selSelectList =
                             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                           selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                           selLimit = Nothing, selOffset = Nothing, selOption = []}),
                 WithQuery (A) (Nmc "b") Nothing
                   (Select{ann = A, selDistinct = All,
                           selSelectList =
                             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                           selTref = [Tref (A) (Name (A) [Nmc "tbl1"])], selWhere = Nothing,
                           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                           selLimit = Nothing, selOffset = Nothing, selOption = []})],
              withQe =
                Select{ann = A, selDistinct = All,
                       selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
                       selTref = [], selWhere = Nothing, selGroupBy = [],
                       selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                       selOffset = Nothing, selOption = []}}

           with a as (select a from tbl),
                b as (select a from tbl1)
                select 1
                union select 2;
WithQueryExpr{ann = A,
              withs =
                [WithQuery (A) (Nmc "a") Nothing
                   (Select{ann = A, selDistinct = All,
                           selSelectList =
                             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                           selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                           selLimit = Nothing, selOffset = Nothing, selOption = []}),
                 WithQuery (A) (Nmc "b") Nothing
                   (Select{ann = A, selDistinct = All,
                           selSelectList =
                             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                           selTref = [Tref (A) (Name (A) [Nmc "tbl1"])], selWhere = Nothing,
                           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                           selLimit = Nothing, selOffset = Nothing, selOption = []})],
              withQe =
                CombineQueryExpr{ann = A, cqType = Union,
                                 cqQe0 =
                                   Select{ann = A, selDistinct = All,
                                          selSelectList =
                                            SelectList (A) [SelExp (A) (NumberLit (A) "1")],
                                          selTref = [], selWhere = Nothing, selGroupBy = [],
                                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                                          selOffset = Nothing, selOption = []},
                                 cqQe1 =
                                   Select{ann = A, selDistinct = All,
                                          selSelectList =
                                            SelectList (A) [SelExp (A) (NumberLit (A) "2")],
                                          selTref = [], selWhere = Nothing, selGroupBy = [],
                                          selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
                                          selOffset = Nothing, selOption = []}}}

6. selectLists

select a from tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a,b from tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
            SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a as b from tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A) (Identifier (A) (Name (A) [Nmc "a"])) (Nmc "b")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select * from tbl
Select{ann = A, selDistinct = All,
       selSelectList = SelectList (A) [SelExp (A) (Star (A))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select tbl.* from tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (QStar (A) (Nmc "tbl"))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a + b as b from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (BinaryOp (A) (Name (A) [Nmc "+"])
                 (Identifier (A) (Name (A) [Nmc "a"]))
                 (Identifier (A) (Name (A) [Nmc "b"])))
              (Nmc "b")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number() over(order by a) as place from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) []) []
                 [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)]
                 Nothing)
              (Nmc "place")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number() over(order by a asc) as place from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) []) []
                 [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)]
                 Nothing)
              (Nmc "place")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number() over(order by a desc) as place from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) []) []
                 [(Identifier (A) (Name (A) [Nmc "a"]), Desc, NullsDefault)]
                 Nothing)
              (Nmc "place")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number()
over(partition by a,b order by c) as place
from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) [])
                 [Identifier (A) (Name (A) [Nmc "a"]),
                  Identifier (A) (Name (A) [Nmc "b"])]
                 [(Identifier (A) (Name (A) [Nmc "c"]), Asc, NullsDefault)]
                 Nothing)
              (Nmc "place")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number() over(), x from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) []) [] []
                 Nothing),
            SelExp (A) (Identifier (A) (Name (A) [Nmc "x"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select count(distinct b) from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (AggregateApp (A) Distinct
                 (App (A) (Name (A) [Nmc "count"])
                    [Identifier (A) (Name (A) [Nmc "b"])])
                 [])],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select count(all b) from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (AggregateApp (A) All
                 (App (A) (Name (A) [Nmc "count"])
                    [Identifier (A) (Name (A) [Nmc "b"])])
                 [])],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select string_agg(distinct relname,',' order by relname1) from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (AggregateApp (A) Distinct
                 (App (A) (Name (A) [Nmc "string_agg"])
                    [Identifier (A) (Name (A) [Nmc "relname"]), StringLit (A) ","])
                 [(Identifier (A) (Name (A) [Nmc "relname1"]), Asc,
                   NullsDefault)])],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a, count(b) from tbl group by a;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
            SelExp (A)
              (App (A) (Name (A) [Nmc "count"])
                 [Identifier (A) (Name (A) [Nmc "b"])])],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [Identifier (A) (Name (A) [Nmc "a"])],
       selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
       selOffset = Nothing, selOption = []}
select a, count(b) as cnt from tbl group by a having cnt > 4;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
            SelectItem (A)
              (App (A) (Name (A) [Nmc "count"])
                 [Identifier (A) (Name (A) [Nmc "b"])])
              (Nmc "cnt")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [Identifier (A) (Name (A) [Nmc "a"])],
       selHaving =
         Just
           (BinaryOp (A) (Name (A) [Nmc ">"])
              (Identifier (A) (Name (A) [Nmc "cnt"]))
              (NumberLit (A) "4")),
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from tbl option (partition group);
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing,
       selOption = [QueryHintPartitionGroup]}
select a from tbl option (partition group,columnar host group);
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing,
       selOption = [QueryHintPartitionGroup, QueryHintColumnarHostGroup]}
select a b from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A) (Identifier (A) (Name (A) [Nmc "a"])) (Nmc "b")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a b, b c, c d from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A) (Identifier (A) (Name (A) [Nmc "a"])) (Nmc "b"),
            SelectItem (A) (Identifier (A) (Name (A) [Nmc "b"])) (Nmc "c"),
            SelectItem (A) (Identifier (A) (Name (A) [Nmc "c"])) (Nmc "d")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a + b b from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (BinaryOp (A) (Name (A) [Nmc "+"])
                 (Identifier (A) (Name (A) [Nmc "a"]))
                 (Identifier (A) (Name (A) [Nmc "b"])))
              (Nmc "b")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select row_number() over(order by a) place from tbl;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelectItem (A)
              (WindowApp (A) (App (A) (Name (A) [Nmc "row_number"]) []) []
                 [(Identifier (A) (Name (A) [Nmc "a"]), Asc, NullsDefault)]
                 Nothing)
              (Nmc "place")],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}

7. tableRefs

select a from tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}
select a from sc.tbl
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "sc", Nmc "tbl"])],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from tbl a
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "a") (Tref (A) (Name (A) [Nmc "tbl"]))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from tbl as a
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "a") (Tref (A) (Name (A) [Nmc "tbl"]))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from tbl as a(b,c)
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [FullAlias (A) (Nmc "a") [Nmc "b", Nmc "c"]
            (Tref (A) (Name (A) [Nmc "tbl"]))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from gen();
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [FunTref (A) (App (A) (Name (A) [Nmc "gen"]) [])],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from gen() t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "t")
            (FunTref (A) (App (A) (Name (A) [Nmc "gen"]) []))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from gen() as t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "t")
            (FunTref (A) (App (A) (Name (A) [Nmc "gen"]) []))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from (select a from tbl) as t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "t")
            (SubTref (A)
               (Select{ann = A, selDistinct = All,
                       selSelectList =
                         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                       selLimit = Nothing, selOffset = Nothing, selOption = []}))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from (select a from tbl) t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableAlias (A) (Nmc "t")
            (SubTref (A)
               (Select{ann = A, selDistinct = All,
                       selSelectList =
                         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                       selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
                       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                       selLimit = Nothing, selOffset = Nothing, selOption = []}))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}

8. joins

select a from t1,t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [Tref (A) (Name (A) [Nmc "t1"]), Tref (A) (Name (A) [Nmc "t2"])],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 natural inner join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Natural Inner
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 inner join t2 using (a)
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural Inner
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            (Just (JoinUsing (A) [Nmc "a"]))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 left outer join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural LeftOuter
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 right outer join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural RightOuter
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 full outer join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural FullOuter
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 cross join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural Cross
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from t1 join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural Inner
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from (b natural join c);
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableRefParens (A)
            (JoinTref (A) (Tref (A) (Name (A) [Nmc "b"])) Natural Inner Nothing
               (Tref (A) (Name (A) [Nmc "c"]))
               Nothing)],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from a cross join b cross join c;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A)
            (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
               Nothing
               (Tref (A) (Name (A) [Nmc "b"]))
               Nothing)
            Unnatural
            Cross
            Nothing
            (Tref (A) (Name (A) [Nmc "c"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from (a cross join b) cross join c;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A)
            (TableRefParens (A)
               (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
                  Nothing
                  (Tref (A) (Name (A) [Nmc "b"]))
                  Nothing))
            Unnatural
            Cross
            Nothing
            (Tref (A) (Name (A) [Nmc "c"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from ((a cross join b) cross join c);
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableRefParens (A)
            (JoinTref (A)
               (TableRefParens (A)
                  (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
                     Nothing
                     (Tref (A) (Name (A) [Nmc "b"]))
                     Nothing))
               Unnatural
               Cross
               Nothing
               (Tref (A) (Name (A) [Nmc "c"]))
               Nothing)],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from a cross join (b cross join c);
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
            Nothing
            (TableRefParens (A)
               (JoinTref (A) (Tref (A) (Name (A) [Nmc "b"])) Unnatural Cross
                  Nothing
                  (Tref (A) (Name (A) [Nmc "c"]))
                  Nothing))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from (a cross join (b cross join c));
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [TableRefParens (A)
            (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
               Nothing
               (TableRefParens (A)
                  (JoinTref (A) (Tref (A) (Name (A) [Nmc "b"])) Unnatural Cross
                     Nothing
                     (Tref (A) (Name (A) [Nmc "c"]))
                     Nothing))
               Nothing)],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from ((a cross join b) cross join c) cross join d;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A)
            (TableRefParens (A)
               (JoinTref (A)
                  (TableRefParens (A)
                     (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
                        Nothing
                        (Tref (A) (Name (A) [Nmc "b"]))
                        Nothing))
                  Unnatural
                  Cross
                  Nothing
                  (Tref (A) (Name (A) [Nmc "c"]))
                  Nothing))
            Unnatural
            Cross
            Nothing
            (Tref (A) (Name (A) [Nmc "d"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from a cross join b cross join c cross join d;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A)
            (JoinTref (A)
               (JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Cross
                  Nothing
                  (Tref (A) (Name (A) [Nmc "b"]))
                  Nothing)
               Unnatural
               Cross
               Nothing
               (Tref (A) (Name (A) [Nmc "c"]))
               Nothing)
            Unnatural
            Cross
            Nothing
            (Tref (A) (Name (A) [Nmc "d"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from b
    inner join c
      on true
    inner join d
      on 1=1;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A)
            (JoinTref (A) (Tref (A) (Name (A) [Nmc "b"])) Unnatural Inner
               Nothing
               (Tref (A) (Name (A) [Nmc "c"]))
               (Just (JoinOn (A) (BooleanLit (A) True))))
            Unnatural
            Inner
            Nothing
            (Tref (A) (Name (A) [Nmc "d"]))
            (Just
               (JoinOn (A)
                  (BinaryOp (A) (Name (A) [Nmc "="]) (NumberLit (A) "1")
                     (NumberLit (A) "1"))))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}

9. dml

9.1. insert

insert into testtable
(columna,columnb)
values (1,2);
[Insert (A) (Name (A) [Nmc "testtable"])
   [Nmc "columna", Nmc "columnb"]
   (Values{ann = A,
           qeValues = [[NumberLit (A) "1", NumberLit (A) "2"]]})
   Nothing]
values (1,2), (3,4);
[QueryStatement (A)
   (Values{ann = A,
           qeValues =
             [[NumberLit (A) "1", NumberLit (A) "2"],
              [NumberLit (A) "3", NumberLit (A) "4"]]})]
insert into testtable
(columna,columnb)
values (1,2), (3,4);
[Insert (A) (Name (A) [Nmc "testtable"])
   [Nmc "columna", Nmc "columnb"]
   (Values{ann = A,
           qeValues =
             [[NumberLit (A) "1", NumberLit (A) "2"],
              [NumberLit (A) "3", NumberLit (A) "4"]]})
   Nothing]
insert into a
    select b from c;
[Insert (A) (Name (A) [Nmc "a"]) []
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "c"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})
   Nothing]
insert into testtable
(columna,columnb)
values (1,2) returning id;
[Insert (A) (Name (A) [Nmc "testtable"])
   [Nmc "columna", Nmc "columnb"]
   (Values{ann = A,
           qeValues = [[NumberLit (A) "1", NumberLit (A) "2"]]})
   (Just
      (SelectList (A)
         [SelExp (A) (Identifier (A) (Name (A) [Nmc "id"]))]))]

9.2. update

update tb
  set x = 1, y = 2;
[Update (A) (Name (A) [Nmc "tb"])
   [SetClause (A) (Nmc "x") (NumberLit (A) "1"),
    SetClause (A) (Nmc "y") (NumberLit (A) "2")]
   []
   Nothing
   Nothing]
update tb
  set x = 1, y = 2 where z = true;
[Update (A) (Name (A) [Nmc "tb"])
   [SetClause (A) (Nmc "x") (NumberLit (A) "1"),
    SetClause (A) (Nmc "y") (NumberLit (A) "2")]
   []
   (Just
      (BinaryOp (A) (Name (A) [Nmc "="])
         (Identifier (A) (Name (A) [Nmc "z"]))
         (BooleanLit (A) True)))
   Nothing]
update tb
  set x = 1, y = 2 returning id;
[Update (A) (Name (A) [Nmc "tb"])
   [SetClause (A) (Nmc "x") (NumberLit (A) "1"),
    SetClause (A) (Nmc "y") (NumberLit (A) "2")]
   []
   Nothing
   (Just
      (SelectList (A)
         [SelExp (A) (Identifier (A) (Name (A) [Nmc "id"]))]))]
update tb
  set (x,y) = (1,2);
[Update (A) (Name (A) [Nmc "tb"])
   [MultiSetClause (A) [Nmc "x", Nmc "y"]
      (SpecialOp (A) (Name (A) [Nmc "rowctor"])
         [NumberLit (A) "1", NumberLit (A) "2"])]
   []
   Nothing
   Nothing]

9.3. delete

delete from tbl1 where x = true;
[Delete (A) (Name (A) [Nmc "tbl1"]) []
   (Just
      (BinaryOp (A) (Name (A) [Nmc "="])
         (Identifier (A) (Name (A) [Nmc "x"]))
         (BooleanLit (A) True)))
   Nothing]
delete from tbl1 where x = true returning id;
[Delete (A) (Name (A) [Nmc "tbl1"]) []
   (Just
      (BinaryOp (A) (Name (A) [Nmc "="])
         (Identifier (A) (Name (A) [Nmc "x"]))
         (BooleanLit (A) True)))
   (Just
      (SelectList (A)
         [SelExp (A) (Identifier (A) (Name (A) [Nmc "id"]))]))]

9.4. truncate

truncate test;
[Truncate (A) [Name (A) [Nmc "test"]] ContinueIdentity Restrict]
truncate table test, test2 restart identity cascade;
[Truncate (A) [Name (A) [Nmc "test"], Name (A) [Nmc "test2"]]
   RestartIdentity
   Cascade]

9.5. copy

copy tbl(a,b) from stdin;
bat	t
bear	f
\.
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"] Stdin [],
 CopyData (A) "bat\tt\nbear\tf\n"]
copy tbl (a,b) from 'filename' with delimiter '|';
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"]
   (CopyFilename "filename")
   [CopyFromDelimiter "|"]]
copy tbl (a,b) from 'filename' with delimiter '|' parsers 'b=oracle';
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"]
   (CopyFilename "filename")
   [CopyFromDelimiter "|", CopyFromParsers "b=oracle"]]
copy tbl (a,b) from 'filename' with delimiter '|' error_log 'errors.log';
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"]
   (CopyFilename "filename")
   [CopyFromDelimiter "|", CopyFromErrorLog "errors.log"]]
copy tbl (a,b) from 'filename' with delimiter '|' error_log 'errors.log' error_verbosity 1;
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"]
   (CopyFilename "filename")
   [CopyFromDelimiter "|", CopyFromErrorLog "errors.log",
    CopyFromErrorVerbosity 1]]
copy tbl (a,b) from 'filename' with delimiter '|' error_log 'errors.log' error_verbosity 1 parsers 'b=oracle';
[CopyFrom (A) (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"]
   (CopyFilename "filename")
   [CopyFromDelimiter "|", CopyFromErrorLog "errors.log",
    CopyFromErrorVerbosity 1, CopyFromParsers "b=oracle"]]
copy tbl to 'file';
[CopyTo (A) (CopyTable (Name (A) [Nmc "tbl"]) []) "file" []]
copy tbl(a,b) to 'file';
[CopyTo (A) (CopyTable (Name (A) [Nmc "tbl"]) [Nmc "a", Nmc "b"])
   "file"
   []]
copy (select * from tbl) to 'file' with format binary;
[CopyTo (A)
   (CopyQuery
      (Select{ann = A, selDistinct = All,
              selSelectList = SelectList (A) [SelExp (A) (Star (A))],
              selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
              selGroupBy = [], selHaving = Nothing, selOrderBy = [],
              selLimit = Nothing, selOffset = Nothing, selOption = []}))
   "file"
   [CopyToFormat "binary"]]

10. ddl

10.1. create table

10.1.1. simple tables

create table test (
  fielda text,
  fieldb int
);
[CreateTable (A) (Name (A) [Nmc "test"])
   [AttributeDef (A) (Nmc "fielda")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "fieldb")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   []
   Nothing
   NoReplace]
create table tbl (
  fld boolean default false);
[CreateTable (A) (Name (A) [Nmc "tbl"])
   [AttributeDef (A) (Nmc "fld")
      (SimpleTypeName (A) (Name (A) [Nmc "boolean"]))
      (Just (BooleanLit (A) False))
      []]
   []
   Nothing
   NoReplace]
create table tbl as select 1;
[CreateTableAs (A) (Name (A) [Nmc "tbl"]) NoReplace
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})]
create table tbl  (
  fld int not null identity(1,1));
[CreateTable (A) (Name (A) [Nmc "tbl"])
   [AttributeDef (A) (Nmc "fld")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [NotNullConstraint (A) "",
       IdentityConstraint (A) "" (Just (1, 1))]]
   []
   Nothing
   NoReplace]
create table tbl  (
  fld int not null identity(-1,-1));
[CreateTable (A) (Name (A) [Nmc "tbl"])
   [AttributeDef (A) (Nmc "fld")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [NotNullConstraint (A) "",
       IdentityConstraint (A) "" (Just (-1, -1))]]
   []
   Nothing
   NoReplace]
create table tbl  (
  fld int not null identity);
[CreateTable (A) (Name (A) [Nmc "tbl"])
   [AttributeDef (A) (Nmc "fld")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [NotNullConstraint (A) "", IdentityConstraint (A) "" Nothing]]
   []
   Nothing
   NoReplace]
alter table a rename to b;
[AlterTable (A) (Name (A) [Nmc "a"])
   (RenameTable (A) (Name (A) [Nmc "b"]))]
alter table a rename column b to c;
[AlterTable (A) (Name (A) [Nmc "a"])
   (RenameColumn (A) (Nmc "b") (Nmc "c"))]
alter table a add column b int;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AddColumn (A)
         (AttributeDef (A) (Nmc "b")
            (SimpleTypeName (A) (Name (A) [Nmc "int"]))
            Nothing
            [])])]
alter table a drop column b;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A) [DropColumn (A) (Nmc "b")])]
alter table a alter column b set data type int;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b")
         (SetDataType (A) (SimpleTypeName (A) (Name (A) [Nmc "int"])))])]
alter table a alter column b set data type int;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b")
         (SetDataType (A) (SimpleTypeName (A) (Name (A) [Nmc "int"])))])]
alter table a alter column b set default 1;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b") (SetDefault (A) (NumberLit (A) "1"))])]
alter table a alter column b drop default;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b") (DropDefault (A))])]
alter table a alter column b set not null;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b") (SetNotNull (A))])]
alter table a alter column b drop not null;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AlterColumn (A) (Nmc "b") (DropNotNull (A))])]
alter table a add column b int,drop column c;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AddColumn (A)
         (AttributeDef (A) (Nmc "b")
            (SimpleTypeName (A) (Name (A) [Nmc "int"]))
            Nothing
            []),
       DropColumn (A) (Nmc "c")])]
alter table a drop column b;
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A) [DropColumn (A) (Nmc "b")])]
alter table a add constraint unique(b);
[AlterTable (A) (Name (A) [Nmc "a"])
   (AlterTableActions (A)
      [AddConstraint (A) (UniqueConstraint (A) "" [Nmc "b"])])]

10.1.2. constraints

10.1.2.1. nulls
create table t1 (
 a text null
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "a")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [NullConstraint (A) ""]]
   []
   Nothing
   NoReplace]
create table t1 (
 a text not null
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "a")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [NotNullConstraint (A) ""]]
   []
   Nothing
   NoReplace]
10.1.2.2. unique
create table t1 (
 x int,
 y int,
 unique (x,y)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [UniqueConstraint (A) "" [Nmc "x", Nmc "y"]]
   Nothing
   NoReplace]
create table t1 (
 x int,
 unique (x),
 y int
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [UniqueConstraint (A) "" [Nmc "x"]]
   Nothing
   NoReplace]
create table t1(
 x int,
 ts datetime
 )
 partition by range(ts)
  (
   every 5 months
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "ts")
      (SimpleTypeName (A) (Name (A) [Nmc "datetime"]))
      Nothing
      []]
   []
   (Just (TablePartitionDef (A) (Nmc "ts") 5 Month))
   NoReplace]
create table t1 (
 x int unique
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowUniqueConstraint (A) ""]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int unique not null
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowUniqueConstraint (A) "", NotNullConstraint (A) ""]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int not null unique
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [NotNullConstraint (A) "", RowUniqueConstraint (A) ""]]
   []
   Nothing
   NoReplace]
10.1.2.3. primary key
create table t1 (
 x int primary key
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowPrimaryKeyConstraint (A) ""]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int,
 y int,
 primary key (x,y)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [PrimaryKeyConstraint (A) "" [Nmc "x", Nmc "y"]]
   Nothing
   NoReplace]
10.1.2.4. check
create table t (
f text check (f in('a', 'b'))
);
[CreateTable (A) (Name (A) [Nmc "t"])
   [AttributeDef (A) (Nmc "f")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [RowCheckConstraint (A) ""
         (InPredicate (A) (Identifier (A) (Name (A) [Nmc "f"])) True
            (InList (A) [StringLit (A) "a", StringLit (A) "b"]))]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int,
 y int,
 check (x>y)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [CheckConstraint (A) ""
      (BinaryOp (A) (Name (A) [Nmc ">"])
         (Identifier (A) (Name (A) [Nmc "x"]))
         (Identifier (A) (Name (A) [Nmc "y"])))]
   Nothing
   NoReplace]
10.1.2.5. misc
create table t (
f text not null unique check (f in('a', 'b'))
);
[CreateTable (A) (Name (A) [Nmc "t"])
   [AttributeDef (A) (Nmc "f")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [NotNullConstraint (A) "", RowUniqueConstraint (A) "",
       RowCheckConstraint (A) ""
         (InPredicate (A) (Identifier (A) (Name (A) [Nmc "f"])) True
            (InList (A) [StringLit (A) "a", StringLit (A) "b"]))]]
   []
   Nothing
   NoReplace]
10.1.2.6. references
create table t1 (
 x int references t2
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowReferenceConstraint (A) "" (Name (A) [Nmc "t2"]) Nothing
         Restrict
         Restrict]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int references t2(y)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowReferenceConstraint (A) "" (Name (A) [Nmc "t2"])
         (Just (Nmc "y"))
         Restrict
         Restrict]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int,
 y int,
 foreign key (x,y) references t2
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [ReferenceConstraint (A) "" [Nmc "x", Nmc "y"]
      (Name (A) [Nmc "t2"])
      []
      Restrict
      Restrict]
   Nothing
   NoReplace]
create table t1 (
 x int,
 y int,
 foreign key (x,y) references t2(z,w)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [ReferenceConstraint (A) "" [Nmc "x", Nmc "y"]
      (Name (A) [Nmc "t2"])
      [Nmc "z", Nmc "w"]
      Restrict
      Restrict]
   Nothing
   NoReplace]
create table t1 (
 x int references t2 on delete cascade
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowReferenceConstraint (A) "" (Name (A) [Nmc "t2"]) Nothing
         Cascade
         Restrict]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int references t2 on update cascade
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowReferenceConstraint (A) "" (Name (A) [Nmc "t2"]) Nothing
         Restrict
         Cascade]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int references t2 on delete cascade on update cascade
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [RowReferenceConstraint (A) "" (Name (A) [Nmc "t2"]) Nothing
         Cascade
         Cascade]]
   []
   Nothing
   NoReplace]
create table t1 (
 x int,
 y int,
 foreign key (x,y) references t2 on update cascade on delete cascade
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [ReferenceConstraint (A) "" [Nmc "x", Nmc "y"]
      (Name (A) [Nmc "t2"])
      []
      Cascade
      Cascade]
   Nothing
   NoReplace]
create or replace table test (
  fielda text,
  fieldb int
);
[CreateTable (A) (Name (A) [Nmc "test"])
   [AttributeDef (A) (Nmc "fielda")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "fieldb")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   []
   Nothing
   Replace]
create or replace table tbl as select 1;
[CreateTableAs (A) (Name (A) [Nmc "tbl"]) Replace
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})]
create or replace table t1 (
 x int,
 y int,
 foreign key (x,y) references t2(z,w)
);
[CreateTable (A) (Name (A) [Nmc "t1"])
   [AttributeDef (A) (Nmc "x")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "y")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   [ReferenceConstraint (A) "" [Nmc "x", Nmc "y"]
      (Name (A) [Nmc "t2"])
      [Nmc "z", Nmc "w"]
      Restrict
      Restrict]
   Nothing
   Replace]

10.2. misc ddl

10.2.1. misc create/alter

create database dbname;
[CreateDatabase (A) (Name (A) [Nmc "dbname"])]
alter database dbname rename to otherdb;
[AlterDatabase (A) (Name (A) [Nmc "dbname"])
   (RenameDatabase (A) (Name (A) [Nmc "otherdb"]))]
create view v1 as
select a,b from t;
[CreateView (A) (Name (A) [Nmc "v1"]) Nothing
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
alter view v1 as
select a,b from t;
[AlterView (A) (Name (A) [Nmc "v1"]) Nothing
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
create view v1(c,d) as
select a,b from t;
[CreateView (A) (Name (A) [Nmc "v1"]) (Just [Nmc "c", Nmc "d"])
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
alter view v1(c,d) as
select a,b from t;
[AlterView (A) (Name (A) [Nmc "v1"]) (Just [Nmc "c", Nmc "d"])
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
create domain td as text check (value in ('t1', 't2'));
[CreateDomain (A) (Name (A) [Nmc "td"])
   (SimpleTypeName (A) (Name (A) [Nmc "text"]))
   ""
   (Just
      (InPredicate (A) (Identifier (A) (Name (A) [Nmc "value"])) True
         (InList (A) [StringLit (A) "t1", StringLit (A) "t2"])))]
create type tp1 as (
  f1 text,
  f2 text
);
[CreateType (A) (Name (A) [Nmc "tp1"])
   [TypeAttDef (A) (Nmc "f1")
      (SimpleTypeName (A) (Name (A) [Nmc "text"])),
    TypeAttDef (A) (Nmc "f2")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))]]
create sequence s start with 5 increment by 4 no maxvalue no minvalue cache 1;
[CreateSequence (A) (Name (A) [Nmc "s"]) 4 Nothing Nothing 5 1]
alter sequence s owned by a.b;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceOwned (A) (Name (A) [Nmc "a", Nmc "b"]))]
alter sequence s rename to s2;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceRename (A) (Name (A) [Nmc "s2"]))]
alter sequence s increment by 2 start with 3;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceActions (A)
      [AlterSequenceIncrement (A) 2, AlterSequenceStart (A) 3])]
alter sequence s no maxvalue minvalue 1;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceActions (A)
      [AlterSequenceMax (A) Nothing, AlterSequenceMin (A) (Just 1)])]
alter sequence s cache 2;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceActions (A) [AlterSequenceCache (A) 2])]
alter sequence s restart;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceActions (A) [AlterSequenceRestart (A) Nothing])]
alter sequence s restart with 1;
[AlterSequence (A) (Name (A) [Nmc "s"])
   (AlterSequenceActions (A) [AlterSequenceRestart (A) (Just 1)])]
create trigger tr
after insert or delete on tb
for each statement
execute procedure fb();
[CreateTrigger (A) (Nmc "tr") TriggerAfter [TInsert, TDelete]
   (Name (A) [Nmc "tb"])
   EachStatement
   (Name (A) [Nmc "fb"])
   []]
drop trigger
if exists tr
on tb cascade;
[DropTrigger (A) IfExists (Nmc "tr") (Name (A) [Nmc "tb"]) Cascade]
drop trigger tr
on tb restrict;
[DropTrigger (A) Require (Nmc "tr") (Name (A) [Nmc "tb"]) Restrict]

10.2.2. drops

drop domain t;
[DropSomething (A) Domain Require [Name (A) [Nmc "t"]] Restrict]
drop domain if exists t,u cascade;
[DropSomething (A) Domain IfExists
   [Name (A) [Nmc "t"], Name (A) [Nmc "u"]]
   Cascade]
drop domain t restrict;
[DropSomething (A) Domain Require [Name (A) [Nmc "t"]] Restrict]
drop type t;
[DropSomething (A) Type Require [Name (A) [Nmc "t"]] Restrict]
drop table t;
[DropSomething (A) Table Require [Name (A) [Nmc "t"]] Restrict]
drop view t;
[DropSomething (A) View Require [Name (A) [Nmc "t"]] Restrict]
drop database dbname;
[DropSomething (A) Database Require [Name (A) [Nmc "dbname"]]
   Restrict]

10.3. functionsddl

10.3.1. basics

create function t1(text) returns text as $$
select a from t1 where b = $1;
$$ language sql stable;
[CreateFunction (A) (Name (A) [Nmc "t1"])
   [ParamDefTp (A) (SimpleTypeName (A) (Name (A) [Nmc "text"]))]
   (SimpleTypeName (A) (Name (A) [Nmc "text"]))
   NoReplace
   Sql
   (SqlFnBody (A)
      [QueryStatement (A)
         (Select{ann = A, selDistinct = All,
                 selSelectList =
                   SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
                 selTref = [Tref (A) (Name (A) [Nmc "t1"])],
                 selWhere =
                   Just
                     (BinaryOp (A) (Name (A) [Nmc "="])
                        (Identifier (A) (Name (A) [Nmc "b"]))
                        (PositionalArg (A) 1)),
                 selGroupBy = [], selHaving = Nothing, selOrderBy = [],
                 selLimit = Nothing, selOffset = Nothing, selOption = []})])
   Stable]
create function fn() returns void as $$
declare
  a int;
  b text;
begin
  null;
end;
$$ language plpgsql volatile;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing
         [VarDef (A) (Nmc "a") (SimpleTypeName (A) (Name (A) [Nmc "int"]))
            Nothing,
          VarDef (A) (Nmc "b") (SimpleTypeName (A) (Name (A) [Nmc "text"]))
            Nothing]
         [NullStatement (A)]))
   Volatile]
create function fn() returns void as $$
declare
  a int;
  b text;
begin
  null;
end;
$$ language plpgsql volatile;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing
         [VarDef (A) (Nmc "a") (SimpleTypeName (A) (Name (A) [Nmc "int"]))
            Nothing,
          VarDef (A) (Nmc "b") (SimpleTypeName (A) (Name (A) [Nmc "text"]))
            Nothing]
         [NullStatement (A)]))
   Volatile]
create function fn(a text[]) returns int[] as $$
declare
  b xtype[] := '{}';
begin
  null;
end;
$$ language plpgsql immutable;
[CreateFunction (A) (Name (A) [Nmc "fn"])
   [ParamDef (A) (Nmc "a")
      (ArrayTypeName (A) (SimpleTypeName (A) (Name (A) [Nmc "text"])))]
   (ArrayTypeName (A) (SimpleTypeName (A) (Name (A) [Nmc "int"])))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing
         [VarDef (A) (Nmc "b")
            (ArrayTypeName (A) (SimpleTypeName (A) (Name (A) [Nmc "xtype"])))
            (Just (StringLit (A) "{}"))]
         [NullStatement (A)]))
   Immutable]
create function fn() returns void as '
declare
  a int := 3;
begin
  null;
end;
' language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing
         [VarDef (A) (Nmc "a") (SimpleTypeName (A) (Name (A) [Nmc "int"]))
            (Just (NumberLit (A) "3"))]
         [NullStatement (A)]))
   Stable]
create function fn(int) returns void as '
declare
  a alias for $1;
begin
  null;
end;
' language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"])
   [ParamDefTp (A) (SimpleTypeName (A) (Name (A) [Nmc "int"]))]
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing [ParamAlias (A) (Nmc "a") 1]
         [NullStatement (A)]))
   Stable]
create function fn(b int) returns void as '
declare
  a alias for b;
begin
  null;
end;
' language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"])
   [ParamDef (A) (Nmc "b")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))]
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A)
      (Block (A) Nothing [VarAlias (A) (Nmc "a") (Name (A) [Nmc "b"])]
         [NullStatement (A)]))
   Stable]
create function fn() returns setof int as $$
begin
  null;
end;
$$ language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SetOfTypeName (A) (SimpleTypeName (A) (Name (A) [Nmc "int"])))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A) (Block (A) Nothing [] [NullStatement (A)]))
   Stable]
create function fn() returns void as $$
begin
  null;
end
$$ language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   NoReplace
   Plpgsql
   (PlpgsqlFnBody (A) (Block (A) Nothing [] [NullStatement (A)]))
   Stable]
create or replace function fn() returns void as $$
begin
  null;
end
$$ language plpgsql stable;
[CreateFunction (A) (Name (A) [Nmc "fn"]) []
   (SimpleTypeName (A) (Name (A) [Nmc "void"]))
   Replace
   Plpgsql
   (PlpgsqlFnBody (A) (Block (A) Nothing [] [NullStatement (A)]))
   Stable]
drop function test(text);
[DropFunction (A) Require
   [(Name (A) [Nmc "test"],
     [SimpleTypeName (A) (Name (A) [Nmc "text"])])]
   Restrict]
drop function test(int,int);
[DropFunction (A) Require
   [(Name (A) [Nmc "test"],
     [SimpleTypeName (A) (Name (A) [Nmc "int"]),
      SimpleTypeName (A) (Name (A) [Nmc "int"])])]
   Restrict]
drop function if exists a(),test(text) cascade;
[DropFunction (A) IfExists
   [(Name (A) [Nmc "a"], []),
    (Name (A) [Nmc "test"],
     [SimpleTypeName (A) (Name (A) [Nmc "text"])])]
   Cascade]

11. schema

11.1. ddl - schemas

create schema test;
[CreateSchema (A) (Nmc "test") Nothing]
create schema test authorization owner;
[CreateSchema (A) (Nmc "test") (Just (Name (A) [Nmc "owner"]))]

11.1.1. drop schema

drop schema test;
[DropSomething (A) Schema Require [Name (A) [Nmc "test"]] Restrict]
drop schema if exists test restrict;
[DropSomething (A) Schema IfExists [Name (A) [Nmc "test"]]
   Restrict]
drop schema test cascade;
[DropSomething (A) Schema Require [Name (A) [Nmc "test"]] Cascade]

11.1.2. alter schema

alter schema test rename to test2;
[AlterSchema (A) (Nmc "test") (AlterSchemaName (A) (Nmc "test2"))]
alter schema test owner to new_owner;
[AlterSchema (A) (Nmc "test")
   (AlterSchemaOwner (A) (Name (A) [Nmc "new_owner"]))]

11.2. ddl - schema-explicit tables

create table s.test (
  fielda text,
  fieldb int
);
[CreateTable (A) (Name (A) [Nmc "s", Nmc "test"])
   [AttributeDef (A) (Nmc "fielda")
      (SimpleTypeName (A) (Name (A) [Nmc "text"]))
      Nothing
      [],
    AttributeDef (A) (Nmc "fieldb")
      (SimpleTypeName (A) (Name (A) [Nmc "int"]))
      Nothing
      []]
   []
   Nothing
   NoReplace]
alter table s.a rename to b;
[AlterTable (A) (Name (A) [Nmc "s", Nmc "a"])
   (RenameTable (A) (Name (A) [Nmc "b"]))]
drop table s.t;
[DropSomething (A) Table Require [Name (A) [Nmc "s", Nmc "t"]]
   Restrict]

11.3. ddl - schema-explicit views

create view s1.v1 as
select a,b from s2.t;
[CreateView (A) (Name (A) [Nmc "s1", Nmc "v1"]) Nothing
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "s2", Nmc "t"])],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
alter view s1.v1 as
select a,b from s2.t;
[AlterView (A) (Name (A) [Nmc "s1", Nmc "v1"]) Nothing
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"])),
                SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "s2", Nmc "t"])],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
drop view s.v;
[DropSomething (A) View Require [Name (A) [Nmc "s", Nmc "v"]]
   Restrict]

11.4. dml - schemas

select a from s.t
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref = [Tref (A) (Name (A) [Nmc "s", Nmc "t"])],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}
select a from s1.t1 natural inner join t2
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
       selTref =
         [JoinTref (A) (Tref (A) (Name (A) [Nmc "s1", Nmc "t1"])) Natural
            Inner
            Nothing
            (Tref (A) (Name (A) [Nmc "t2"]))
            Nothing],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}

12. plpgsql

12.1. simple plpgsql statements

success := true;
[Assignment (A) (Name (A) [Nmc "success"]) (BooleanLit (A) True)]
success = true;
[Assignment (A) (Name (A) [Nmc "success"]) (BooleanLit (A) True)]
return true;
[Return (A) (Just (BooleanLit (A) True))]
return;
[Return (A) Nothing]
return next 1;
[ReturnNext (A) (NumberLit (A) "1")]
return query select a from b;
[ReturnQuery (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "b"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
raise notice 'stuff %', 1;
[Raise (A) RNotice "stuff %" [NumberLit (A) "1"]]
perform test();
[Perform (A) (App (A) (Name (A) [Nmc "test"]) [])]
perform test(a,b);
[Perform (A)
   (App (A) (Name (A) [Nmc "test"])
      [Identifier (A) (Name (A) [Nmc "a"]),
       Identifier (A) (Name (A) [Nmc "b"])])]
perform test(r.relvar_name || '_and_stuff');
[Perform (A)
   (App (A) (Name (A) [Nmc "test"])
      [BinaryOp (A) (Name (A) [Nmc "||"])
         (Identifier (A) (Name (A) [Nmc "r", Nmc "relvar_name"]))
         (StringLit (A) "_and_stuff")])]
select into a,b c,d from e;
[Into (A) False [Name (A) [Nmc "a"], Name (A) [Nmc "b"]]
   (QueryStatement (A)
      (Select{ann = A, selDistinct = All,
              selSelectList =
                SelectList (A)
                  [SelExp (A) (Identifier (A) (Name (A) [Nmc "c"])),
                   SelExp (A) (Identifier (A) (Name (A) [Nmc "d"]))],
              selTref = [Tref (A) (Name (A) [Nmc "e"])], selWhere = Nothing,
              selGroupBy = [], selHaving = Nothing, selOrderBy = [],
              selLimit = Nothing, selOffset = Nothing, selOption = []}))]
select c,d into a,b from e;
[Into (A) False [Name (A) [Nmc "a"], Name (A) [Nmc "b"]]
   (QueryStatement (A)
      (Select{ann = A, selDistinct = All,
              selSelectList =
                SelectList (A)
                  [SelExp (A) (Identifier (A) (Name (A) [Nmc "c"])),
                   SelExp (A) (Identifier (A) (Name (A) [Nmc "d"]))],
              selTref = [Tref (A) (Name (A) [Nmc "e"])], selWhere = Nothing,
              selGroupBy = [], selHaving = Nothing, selOrderBy = [],
              selLimit = Nothing, selOffset = Nothing, selOption = []}))]
delete from pieces where x = 1 and y = 1 returning tag into r.tag;
[Into (A) False [Name (A) [Nmc "r", Nmc "tag"]]
   (Delete (A) (Name (A) [Nmc "pieces"]) []
      (Just
         (BinaryOp (A) (Name (A) [Nmc "and"])
            (BinaryOp (A) (Name (A) [Nmc "="])
               (Identifier (A) (Name (A) [Nmc "x"]))
               (NumberLit (A) "1"))
            (BinaryOp (A) (Name (A) [Nmc "="])
               (Identifier (A) (Name (A) [Nmc "y"]))
               (NumberLit (A) "1"))))
      (Just
         (SelectList (A)
            [SelExp (A) (Identifier (A) (Name (A) [Nmc "tag"]))])))]
update pieces
set a=b returning tag into r.tag;
[Into (A) False [Name (A) [Nmc "r", Nmc "tag"]]
   (Update (A) (Name (A) [Nmc "pieces"])
      [SetClause (A) (Nmc "a") (Identifier (A) (Name (A) [Nmc "b"]))]
      []
      Nothing
      (Just
         (SelectList (A)
            [SelExp (A) (Identifier (A) (Name (A) [Nmc "tag"]))])))]
insert into t(a) values (1) returning id into x;
[Into (A) False [Name (A) [Nmc "x"]]
   (Insert (A) (Name (A) [Nmc "t"]) [Nmc "a"]
      (Values{ann = A, qeValues = [[NumberLit (A) "1"]]})
      (Just
         (SelectList (A)
            [SelExp (A) (Identifier (A) (Name (A) [Nmc "id"]))])))]
update t
  set x = 1 returning id into z;
[Into (A) False [Name (A) [Nmc "z"]]
   (Update (A) (Name (A) [Nmc "t"])
      [SetClause (A) (Nmc "x") (NumberLit (A) "1")]
      []
      Nothing
      (Just
         (SelectList (A)
            [SelExp (A) (Identifier (A) (Name (A) [Nmc "id"]))])))]
execute s;
[Execute (A) (Identifier (A) (Name (A) [Nmc "s"]))]
execute s into r;
[Into (A) False [Name (A) [Nmc "r"]]
   (Execute (A) (Identifier (A) (Name (A) [Nmc "s"])))]
continue;
[ContinueStatement (A) Nothing]

12.2. other plpgsql statements

for r in select a from tbl loop
null;
end loop;
[ForQueryStatement (A) Nothing (Nmc "r")
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})
   [NullStatement (A)]]
for r in select a from tbl where true loop
null;
end loop;
[ForQueryStatement (A) Nothing (Nmc "r")
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "tbl"])],
           selWhere = Just (BooleanLit (A) True), selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})
   [NullStatement (A)]]
for r in 1 .. 10 loop
null;
end loop;
[ForIntegerStatement (A) Nothing (Nmc "r") (NumberLit (A) "1")
   (NumberLit (A) "10")
   [NullStatement (A)]]
for r in 1..10 loop
null;
end loop;
[ForIntegerStatement (A) Nothing (Nmc "r") (NumberLit (A) "1")
   (NumberLit (A) "10")
   [NullStatement (A)]]
if a=b then
  update c set d = e;
end if;
[If (A)
   [(BinaryOp (A) (Name (A) [Nmc "="])
       (Identifier (A) (Name (A) [Nmc "a"]))
       (Identifier (A) (Name (A) [Nmc "b"])),
     [Update (A) (Name (A) [Nmc "c"])
        [SetClause (A) (Nmc "d") (Identifier (A) (Name (A) [Nmc "e"]))]
        []
        Nothing
        Nothing])]
   []]
if true then
  null;
else
  null;
end if;
[If (A) [(BooleanLit (A) True, [NullStatement (A)])]
   [NullStatement (A)]]
if true then
  null;
elseif false then
  return;
end if;
[If (A)
   [(BooleanLit (A) True, [NullStatement (A)]),
    (BooleanLit (A) False, [Return (A) Nothing])]
   []]
if true then
  null;
elseif false then
  return;
elsif false then
  return;
else
  return;
end if;
[If (A)
   [(BooleanLit (A) True, [NullStatement (A)]),
    (BooleanLit (A) False, [Return (A) Nothing]),
    (BooleanLit (A) False, [Return (A) Nothing])]
   [Return (A) Nothing]]
case a
  when b then null;
  when c,d then null;
  else null;
end case;
[CaseStatementSimple (A) (Identifier (A) (Name (A) [Nmc "a"]))
   [([Identifier (A) (Name (A) [Nmc "b"])], [NullStatement (A)]),
    ([Identifier (A) (Name (A) [Nmc "c"]),
      Identifier (A) (Name (A) [Nmc "d"])],
     [NullStatement (A)])]
   [NullStatement (A)]]

13. miscParserTests

13.1. multiple statements

select 1;
select 2;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []}),
 QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "2")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})]

13.2. comments

[]
-- this is a test
[]
/* this is
a test*/
[]
select 1;
-- this is a test
select -- this is a test
2;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []}),
 QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "2")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})]
select 1;
/* this is
a test*/
select /* this is a test*/2;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "1")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []}),
 QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (NumberLit (A) "2")],
           selTref = [], selWhere = Nothing, selGroupBy = [],
           selHaving = Nothing, selOrderBy = [], selLimit = Nothing,
           selOffset = Nothing, selOption = []})]

13.3. misc

SET search_path TO my_schema, public;
[Set (A) "search_path" [SetId (A) "my_schema", SetId (A) "public"]]
SET t1 = 3;
[Set (A) "t1" [SetNum (A) 3.0]]
SET t1 = 'stuff';
[Set (A) "t1" [SetStr (A) "stuff"]]
create language plpgsql;
[CreateLanguage (A) "plpgsql"]

14. parse sql server

select top 3 * from a order by c;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref = [Tref (A) (Name (A) [Nmc "a"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing,
           selOrderBy =
             [(Identifier (A) (Name (A) [Nmc "c"]), Asc, NullsDefault)],
           selLimit = Just (NumberLit (A) "3"), selOffset = Nothing,
           selOption = []})]
select top(3) * from a order by c;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref = [Tref (A) (Name (A) [Nmc "a"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing,
           selOrderBy =
             [(Identifier (A) (Name (A) [Nmc "c"]), Asc, NullsDefault)],
           selLimit = Just (NumberLit (A) "3"), selOffset = Nothing,
           selOption = []})]
select r.dn as 'rg' from tbl;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelectItem (A) (Identifier (A) (Name (A) [Nmc "r", Nmc "dn"]))
                  (QNmc "rg")],
           selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select r.dn as 'check the pretty printing' from tbl;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelectItem (A) (Identifier (A) (Name (A) [Nmc "r", Nmc "dn"]))
                  (QNmc "check the pretty printing")],
           selTref = [Tref (A) (Name (A) [Nmc "tbl"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select a..b() from t;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A) (App (A) (Name (A) [Nmc "a", Nmc "", Nmc "b"]) [])],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select a...b() from t;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (App (A) (Name (A) [Nmc "a", Nmc "", Nmc "", Nmc "b"]) [])],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select * from a join x..b;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Inner
                Nothing
                (Tref (A) (Name (A) [Nmc "x", Nmc "", Nmc "b"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from a join x...b;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "a"])) Unnatural Inner
                Nothing
                (Tref (A) (Name (A) [Nmc "x", Nmc "", Nmc "", Nmc "b"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select a from t with(nolock);
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select a from #tbl;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "#tbl"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
CREATE TABLE [schema].[table_name](
             [fieldname] [typename])
[CreateTable (A) (Name (A) [QNmc "schema", QNmc "table_name"])
   [AttributeDef (A) (QNmc "fieldname")
      (SimpleTypeName (A) (Name (A) [QNmc "typename"]))
      Nothing
      []]
   []
   Nothing
   NoReplace]
select a from t  -- no semi colon
select b from t
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []}),
 QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
if 1=1
   drop table #temp
select b from t
[If (A)
   [(BinaryOp (A) (Name (A) [Nmc "="]) (NumberLit (A) "1")
       (NumberLit (A) "1"),
     [DropSomething (A) Table Require [Name (A) [Nmc "#temp"]]
        Restrict])]
   [],
 QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "b"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
declare @nm int
[DeclareStatement (A)
   [("@nm", SimpleTypeName (A) (Name (A) [Nmc "int"]), Nothing)]]
declare @nm int = 3, @nm2 datetime = '1/1/2000'
[DeclareStatement (A)
   [("@nm", SimpleTypeName (A) (Name (A) [Nmc "int"]),
     Just (NumberLit (A) "3")),
    ("@nm2", SimpleTypeName (A) (Name (A) [Nmc "datetime"]),
     Just (StringLit (A) "1/1/2000"))]]
select convert (INT,5) from t
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (App (A) (Name (A) [Nmc "convert"])
                     [Identifier (A) (Name (A) [Nmc "INT"]), NumberLit (A) "5"])],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select convert (time,something,108) from t
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (Cast (A) (Identifier (A) (Name (A) [Nmc "something"]))
                     (SimpleTypeName (A) (Name (A) [Nmc "time"])))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
CREATE NONCLUSTERED INDEX idx ON tbl (col) INCLUDE (Gap)
[CreateIndexTSQL (A) (Nmc "idx") (Name (A) [Nmc "tbl"])
   [Nmc "col"]]
CREATE NONCLUSTERED INDEX idx ON [dbo].[#tmp] (col) INCLUDE (Gap)
[CreateIndexTSQL (A) (Nmc "idx")
   (Name (A) [QNmc "dbo", QNmc "#tmp"])
   [Nmc "col"]]
select y -@test from t
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (BinaryOp (A) (Name (A) [Nmc "-"])
                     (Identifier (A) (Name (A) [Nmc "y"]))
                     (Identifier (A) (Name (A) [Nmc "@test"])))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select * from t natural inner hash join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Natural Inner
                (Just Hash)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from t natural inner loop join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Natural Inner
                (Just Loop)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from t natural inner merge join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Natural Inner
                (Just Merge)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from t loop join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Unnatural Inner
                (Just Loop)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from t merge join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Unnatural Inner
                (Just Merge)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]
select * from t hash join u
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [JoinTref (A) (Tref (A) (Name (A) [Nmc "t"])) Unnatural Inner
                (Just Hash)
                (Tref (A) (Name (A) [Nmc "u"]))
                Nothing],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]

15. parse oracle

select unique a from t;
[QueryStatement (A)
   (Select{ann = A, selDistinct = Distinct,
           selSelectList =
             SelectList (A) [SelExp (A) (Identifier (A) (Name (A) [Nmc "a"]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]
select count(unique a) from t;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (AggregateApp (A) Distinct
                     (App (A) (Name (A) [Nmc "count"])
                        [Identifier (A) (Name (A) [Nmc "a"])])
                     [])],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]

16. odbcParsing

16.1. datetime

{d '2000-01-01'}
OdbcLiteral (A) OLDate "2000-01-01"
{t '12:00:01.1'}
OdbcLiteral (A) OLTime "12:00:01.1"
{ts '2000-01-01 12:00:01.1'}
OdbcLiteral (A) OLTimestamp "2000-01-01 12:00:01.1"

16.2. functions

{fn CHARACTER_LENGTH(string_exp)}
OdbcFunc (A)
  (App (A) (Name (A) [Nmc "CHARACTER_LENGTH"])
     [Identifier (A) (Name (A) [Nmc "string_exp"])])
{fn EXTRACT(year from my_date)}
OdbcFunc (A)
  (Extract (A) ExtractYear
     (Identifier (A) (Name (A) [Nmc "my_date"])))
{fn now()}
OdbcFunc (A) (App (A) (Name (A) [Nmc "now"]) [])
{fn CONVERT('2000-01-01', SQL_DATE)}
OdbcFunc (A)
  (App (A) (Name (A) [Nmc "CONVERT"])
     [StringLit (A) "2000-01-01",
      Identifier (A) (Name (A) [Nmc "SQL_DATE"])])
{fn CONVERT({fn CURDATE()}, SQL_DATE)}
OdbcFunc (A)
  (App (A) (Name (A) [Nmc "CONVERT"])
     [OdbcFunc (A) (App (A) (Name (A) [Nmc "CURDATE"]) []),
      Identifier (A) (Name (A) [Nmc "SQL_DATE"])])

16.3. outer join

select * from {oj t1 left outer join t2 on true}
Select{ann = A, selDistinct = All,
       selSelectList = SelectList (A) [SelExp (A) (Star (A))],
       selTref =
         [OdbcTableRef (A)
            (JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural LeftOuter
               Nothing
               (Tref (A) (Name (A) [Nmc "t2"]))
               (Just (JoinOn (A) (BooleanLit (A) True))))],
       selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
       selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
       selOption = []}

16.4. check parsing bugs

select {fn CONVERT(cint,SQL_BIGINT)} from t;
Select{ann = A, selDistinct = All,
       selSelectList =
         SelectList (A)
           [SelExp (A)
              (OdbcFunc (A)
                 (App (A) (Name (A) [Nmc "CONVERT"])
                    [Identifier (A) (Name (A) [Nmc "cint"]),
                     Identifier (A) (Name (A) [Nmc "SQL_BIGINT"])]))],
       selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
       selGroupBy = [], selHaving = Nothing, selOrderBy = [],
       selLimit = Nothing, selOffset = Nothing, selOption = []}

16.5. outer join

select * from {oj t1 left outer join t2 on true}
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList = SelectList (A) [SelExp (A) (Star (A))],
           selTref =
             [OdbcTableRef (A)
                (JoinTref (A) (Tref (A) (Name (A) [Nmc "t1"])) Unnatural LeftOuter
                   Nothing
                   (Tref (A) (Name (A) [Nmc "t2"]))
                   (Just (JoinOn (A) (BooleanLit (A) True))))],
           selWhere = Nothing, selGroupBy = [], selHaving = Nothing,
           selOrderBy = [], selLimit = Nothing, selOffset = Nothing,
           selOption = []})]

16.6. check parsing bugs

select {fn CONVERT(cint,SQL_BIGINT)} from t;
[QueryStatement (A)
   (Select{ann = A, selDistinct = All,
           selSelectList =
             SelectList (A)
               [SelExp (A)
                  (OdbcFunc (A)
                     (App (A) (Name (A) [Nmc "convert"])
                        [Identifier (A) (Name (A) [Nmc "cint"]),
                         Identifier (A) (Name (A) [Nmc "SQL_BIGINT"])]))],
           selTref = [Tref (A) (Name (A) [Nmc "t"])], selWhere = Nothing,
           selGroupBy = [], selHaving = Nothing, selOrderBy = [],
           selLimit = Nothing, selOffset = Nothing, selOption = []})]