PostgresqlSyntax.Parsing

Our parsing strategy is to port the original Postgres parser as closely as possible.

We're using the gram.y Postgres source file, which is the closest thing we have to a Postgres syntax spec. Here's a link to it: https://github.com/postgres/postgres/blob/master/src/backend/parser/gram.y.

Here's the essence of how the original parser is implemented, citing from PostgreSQL Wiki:

scan.l defines the lexer, i.e. the algorithm that splits a string (containing an SQL statement) into a stream of tokens. A token is usually a single word (i.e., doesn't contain spaces but is delimited by spaces), but can also be a whole single or double-quoted string for example. The lexer is basically defined in terms of regular expressions which describe the different token types.

gram.y defines the grammar (the syntactical structure) of SQL statements, using the tokens generated by the lexer as basic building blocks. The grammar is defined in BNF notation. BNF resembles regular expressions but works on the level of tokens, not characters. Also, patterns (called rules or productions in BNF) are named, and may be recursive, i.e. use themselves as sub-patterns.

Documentation

>>> testParser parser = either putStr print . run parser

type Parser = HeadedParsec Void Text

Executors

run :: Parser a -> Text -> Either String a

Helpers

commaSeparator :: Parser ()

dotSeparator :: Parser ()

inBrackets :: Parser a -> Parser a

inBracketsCont :: Parser a -> Parser (Parser a)

inParens :: Parser a -> Parser a

inParensCont :: Parser a -> Parser (Parser a)

inParensWithLabel :: (label -> content -> result) -> Parser label -> Parser content -> Parser result

inParensWithClause :: Parser clause -> Parser content -> Parser content

trueIfPresent :: Parser a -> Parser Bool

quotedString :: Char -> Parser Text

>>> testParser (quotedString '\'') "'abc''d'"
"abc'd"

atEnd :: Parser a -> Parser a

PreparableStmt

preparableStmt :: Parser PreparableStmt

Call

callStmt :: HeadedParsec Void Text CallStmt

Insert

insertStmt :: HeadedParsec Void Text InsertStmt

insertTarget :: HeadedParsec Void Text InsertTarget

insertRest :: HeadedParsec Void Text InsertRest

overrideKind :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s OverrideKind

insertColumnList :: Parser InsertColumnList

insertColumnItem :: HeadedParsec Void Text InsertColumnItem

onConflict :: HeadedParsec Void Text OnConflict

confExpr :: HeadedParsec Void Text ConfExpr

onConflictDo :: HeadedParsec Void Text OnConflictDo

returningClause :: HeadedParsec Void Text ReturningClause

Update

updateStmt :: HeadedParsec Void Text UpdateStmt

setClauseList :: HeadedParsec Void Text (NonEmpty SetClause)

setClause :: HeadedParsec Void Text SetClause

setTarget :: HeadedParsec Void Text SetTarget

setTargetList :: Parser (NonEmpty SetTarget)

Delete

deleteStmt :: HeadedParsec Void Text DeleteStmt

usingClause :: HeadedParsec Void Text UsingClause

Select

selectStmt :: HeadedParsec Void Text SelectStmt

>>> test = testParser selectStmt

>>> test "select id from as"
...
  |
1 | select id from as
  |                  ^
Reserved keyword "as" used as an identifier. If that's what you intend, you have to wrap it in double quotes.

selectWithParens :: HeadedParsec Void Text SelectWithParens

selectNoParens :: HeadedParsec Void Text SelectNoParens

sharedSelectNoParens :: Maybe WithClause -> HeadedParsec Void Text SelectNoParens

simpleSelectNoParens :: HeadedParsec Void Text SelectNoParens

The one that doesn't start with "WITH".

References

  | simple_select
  | select_clause sort_clause
  | select_clause opt_sort_clause for_locking_clause opt_select_limit
  | select_clause opt_sort_clause select_limit opt_for_locking_clause

withSelectNoParens :: HeadedParsec Void Text SelectNoParens

selectClause :: HeadedParsec Void Text (Either SimpleSelect SelectWithParens)

baseSimpleSelect :: HeadedParsec Void Text SimpleSelect

extensionSimpleSelect :: Either SimpleSelect SelectWithParens -> HeadedParsec Void Text SimpleSelect

allOrDistinct :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s Bool

selectBinOp :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s SelectBinOp

valuesClause :: HeadedParsec Void Text ValuesClause

withClause :: HeadedParsec Void Text WithClause

commonTableExpr :: HeadedParsec Void Text CommonTableExpr

materialized :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s Bool

targeting :: HeadedParsec Void Text Targeting

targetList :: HeadedParsec Void Text (NonEmpty TargetEl)

targetEl :: HeadedParsec Void Text TargetEl

>>> testParser targetEl "a.b as c"
AliasedExprTargetEl (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "a") (Just (AttrNameIndirectionEl (UnquotedIdent "b") :| []))))) (UnquotedIdent "c")

onExpressionsClause :: HeadedParsec Void Text ExprList

Into clause details

optTempTableName :: HeadedParsec Void Text IntoClause

References

OptTempTableName:
  | TEMPORARY opt_table qualified_name
  | TEMP opt_table qualified_name
  | LOCAL TEMPORARY opt_table qualified_name
  | LOCAL TEMP opt_table qualified_name
  | GLOBAL TEMPORARY opt_table qualified_name
  | GLOBAL TEMP opt_table qualified_name
  | UNLOGGED opt_table qualified_name
  | TABLE qualified_name
  | qualified_name

Group by details

groupByItem :: HeadedParsec Void Text GroupByItem

Window clause details

windowDefinition :: HeadedParsec Void Text WindowDefinition

windowSpecification :: HeadedParsec Void Text WindowSpecification

References

window_specification:
  |  '(' opt_existing_window_name opt_partition_clause
            opt_sort_clause opt_frame_clause ')'

partitionByClause :: HeadedParsec Void Text PartitionClause

frameClause :: HeadedParsec Void Text FrameClause

References

opt_frame_clause:
  |  RANGE frame_extent opt_window_exclusion_clause
  |  ROWS frame_extent opt_window_exclusion_clause
  |  GROUPS frame_extent opt_window_exclusion_clause
  |  EMPTY

frameClauseMode :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s FrameClauseMode

frameExtent :: HeadedParsec Void Text FrameExtent

frameBound :: HeadedParsec Void Text FrameBound

References

  |  UNBOUNDED PRECEDING
  |  UNBOUNDED FOLLOWING
  |  CURRENT_P ROW
  |  a_expr PRECEDING
  |  a_expr FOLLOWING

windowExclusionClause :: (Token s ~ Char, Tokens s ~ Text, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s WindowExclusionClause

Table refs

fromList :: HeadedParsec Void Text FromClause

fromClause :: HeadedParsec Void Text FromClause

tableRef :: HeadedParsec Void Text TableRef

>>> testParser tableRef "a left join b on (a.i = b.i)"
JoinTableRef (MethJoinedTable (QualJoinMeth...

nonTrailingTableRef :: HeadedParsec Void Text TableRef

trailingTableRef :: TableRef -> HeadedParsec Void Text TableRef

relationExpr :: HeadedParsec Void Text RelationExpr

relationExprOptAlias :: [Text] -> HeadedParsec Void Text RelationExprOptAlias

tablesampleClause :: HeadedParsec Void Text TablesampleClause

repeatableClause :: HeadedParsec Void Text RepeatableClause

funcTable :: HeadedParsec Void Text FuncTable

rowsfromItem :: HeadedParsec Void Text RowsfromItem

rowsfromList :: HeadedParsec Void Text (NonEmpty RowsfromItem)

colDefList :: HeadedParsec Void Text ColDefList

optOrdinality :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s Text

tableFuncElementList :: HeadedParsec Void Text ColDefList

tableFuncElement :: HeadedParsec Void Text TableFuncElement

collateClause :: HeadedParsec Void Text AnyName

funcAliasClause :: HeadedParsec Void Text FuncAliasClause

joinedTable :: Parser JoinedTable

inParensJoinedTable :: HeadedParsec Void Text JoinedTable

References

  | '(' joined_table ')'

trailingJoinedTable :: TableRef -> HeadedParsec Void Text JoinedTable

References

  | table_ref CROSS JOIN table_ref
  | table_ref join_type JOIN table_ref join_qual
  | table_ref JOIN table_ref join_qual
  | table_ref NATURAL join_type JOIN table_ref
  | table_ref NATURAL JOIN table_ref

joinType :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s JoinType

joinQual :: HeadedParsec Void Text JoinQual

aliasClause :: HeadedParsec Void Text AliasClause

Where

whereClause :: HeadedParsec Void Text WhereClause

whereOrCurrentClause :: HeadedParsec Void Text WhereOrCurrentClause

Sorting

sortClause :: HeadedParsec Void Text SortClause

sortBy :: HeadedParsec Void Text SortBy

Expressions

exprList :: HeadedParsec Void Text ExprList

exprListInParens :: HeadedParsec Void Text TypeModifiers

aExpr :: Parser AExpr

Notice that the tree constructed by this parser does not reflect the precedence order of Postgres. For the purposes of this library it simply doesn't matter, so we're not bothering with that.

Composite on the right:

>>> testParser aExpr "a = b :: int4"
SymbolicBinOpAExpr (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "a") Nothing))) (MathSymbolicExprBinOp EqualsMathOp) (TypecastAExpr (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "b") Nothing))) (Typename False (GenericTypeSimpleTypename (GenericType (UnquotedIdent "int4") Nothing Nothing)) False Nothing))

Composite on the left:

>>> testParser aExpr "a = b :: int4 and c"
SymbolicBinOpAExpr (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "a") Nothing))) (MathSymbolicExprBinOp EqualsMathOp) (AndAExpr (TypecastAExpr (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "b") Nothing))) (Typename False (GenericTypeSimpleTypename (GenericType (UnquotedIdent "int4") Nothing Nothing)) False Nothing)) (CExprAExpr (ColumnrefCExpr (Columnref (UnquotedIdent "c") Nothing))))

filteredAExpr :: [Text] -> HeadedParsec Void Text AExpr

customizedAExpr :: HeadedParsec Void Text CExpr -> Parser AExpr

bExpr :: HeadedParsec Void Text BExpr

customizedBExpr :: HeadedParsec Void Text CExpr -> HeadedParsec Void Text BExpr

cExpr :: HeadedParsec Void Text CExpr

customizedCExpr :: HeadedParsec Void Text Columnref -> HeadedParsec Void Text CExpr

subqueryOp :: HeadedParsec Void Text SubqueryOp

subType :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s SubType

inExpr :: HeadedParsec Void Text InExpr

symbolicBinOpExpr :: t -> HeadedParsec Void Text t -> (t -> SymbolicExprBinOp -> t -> b) -> HeadedParsec Void Text b

typecastExpr :: a -> (a -> Typename -> a) -> HeadedParsec Void Text a

plusedExpr :: (Token strm ~ Char, Stream strm, Ord err) => HeadedParsec err strm b -> HeadedParsec err strm b

minusedExpr :: (Token strm ~ Char, Stream strm, Ord err) => HeadedParsec err strm b -> HeadedParsec err strm b

qualOpExpr :: HeadedParsec Void Text a -> (QualOp -> a -> b) -> HeadedParsec Void Text b

row :: HeadedParsec Void Text Row

explicitRow :: HeadedParsec Void Text ExplicitRow

implicitRow :: HeadedParsec Void Text ImplicitRow

arrayExprCont :: HeadedParsec Void Text (HeadedParsec Void Text ArrayExpr)

caseExpr :: HeadedParsec Void Text CaseExpr

whenClause :: HeadedParsec Void Text WhenClause

elseClause :: HeadedParsec Void Text CaseDefault

funcExpr :: HeadedParsec Void Text FuncExpr

funcExprWindowless :: HeadedParsec Void Text FuncExprWindowless

withinGroupClause :: HeadedParsec Void Text WithinGroupClause

filterClause :: HeadedParsec Void Text FilterClause

overClause :: HeadedParsec Void Text OverClause

funcExprCommonSubexpr :: HeadedParsec Void Text FuncExprCommonSubexpr

extractList :: HeadedParsec Void Text ExtractList

extractArg :: HeadedParsec Void Text ExtractArg

overlayList :: HeadedParsec Void Text OverlayList

overlayPlacing :: HeadedParsec Void Text AExpr

positionList :: HeadedParsec Void Text PositionList

substrList :: HeadedParsec Void Text SubstrList

substrListFromFor :: HeadedParsec Void Text SubstrListFromFor

substrFrom :: HeadedParsec Void Text AExpr

substrFor :: HeadedParsec Void Text SubstrFor

trimModifier :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s TrimModifier

trimList :: HeadedParsec Void Text TrimList

funcApplication :: HeadedParsec Void Text FuncApplication

funcApplicationParams :: HeadedParsec Void Text FuncApplicationParams

normalFuncApplicationParams :: HeadedParsec Void Text FuncApplicationParams

singleVariadicFuncApplicationParams :: HeadedParsec Void Text FuncApplicationParams

listVariadicFuncApplicationParams :: HeadedParsec Void Text FuncApplicationParams

starFuncApplicationParams :: (Token strm ~ Char, Stream strm, Ord err) => HeadedParsec err strm FuncApplicationParams

funcArgExpr :: HeadedParsec Void Text FuncArgExpr

References

func_arg_expr:
  | a_expr
  | param_name COLON_EQUALS a_expr
  | param_name EQUALS_GREATER a_expr
param_name:
  | type_function_name

Ops

symbolicExprBinOp :: HeadedParsec Void Text SymbolicExprBinOp

lexicalExprBinOp :: (Token s ~ Char, Tokens s ~ Text, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s Text

qualOp :: HeadedParsec Void Text QualOp

qualAllOp :: HeadedParsec Void Text QualAllOp

op :: (Tokens strm ~ Text, Token strm ~ Char, Ord err, Stream strm) => HeadedParsec err strm Text

anyOperator :: HeadedParsec Void Text AnyOperator

allOp :: (Tokens strm ~ Text, Token strm ~ Char, Ord err, Stream strm, FoldCase (Tokens strm), IsString (Tokens strm)) => HeadedParsec err strm AllOp

mathOp :: (Token strm ~ Char, Stream strm, FoldCase (Tokens strm), IsString (Tokens strm), Ord err) => HeadedParsec err strm MathOp

Constants

aexprConst :: HeadedParsec Void Text AexprConst

>>> testParser aexprConst "32948023849023"
IAexprConst 32948023849023

>>> testParser aexprConst "'abc''de'"
SAexprConst "abc'de"

>>> testParser aexprConst "23.43234"
FAexprConst 23.43234

>>> testParser aexprConst "32423423.324324872"
FAexprConst 3.2423423324324872e7

>>> testParser aexprConst "NULL"
NullAexprConst

References

AexprConst: Iconst
      | FCONST
      | Sconst
      | BCONST
      | XCONST
      | func_name Sconst
      | func_name '(' func_arg_list opt_sort_clause ')' Sconst
      | ConstTypename Sconst
      | ConstInterval Sconst opt_interval
      | ConstInterval '(' Iconst ')' Sconst
      | TRUE_P
      | FALSE_P
      | NULL_P

iconstOrFconst :: (Token strm ~ Char, Stream strm, RealFloat a, Integral a, Ord err) => HeadedParsec err strm (Either a a)

iconst :: (Token strm ~ Char, Stream strm, Integral decimal, Ord err) => HeadedParsec err strm decimal

fconst :: (Token strm ~ Char, Stream strm, RealFloat float, Ord err) => HeadedParsec err strm float

sconst :: Parser Text

constTypename :: HeadedParsec Void Text ConstTypename

numeric :: HeadedParsec Void Text Numeric

bit :: HeadedParsec Void Text Bit

constBit :: HeadedParsec Void Text ConstBit

constCharacter :: HeadedParsec Void Text ConstCharacter

character :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s Character

constDatetime :: HeadedParsec Void Text ConstDatetime

References

ConstDatetime:
  | TIMESTAMP '(' Iconst ')' opt_timezone
  | TIMESTAMP opt_timezone
  | TIME '(' Iconst ')' opt_timezone
  | TIME opt_timezone

timezone :: (Token s ~ Char, Tokens s ~ Text, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s Bool

interval :: HeadedParsec Void Text Interval

intervalSecond :: Integral a => HeadedParsec Void Text (Maybe a)

Clauses

selectLimit :: HeadedParsec Void Text SelectLimit

References

select_limit:
  | limit_clause offset_clause
  | offset_clause limit_clause
  | limit_clause
  | offset_clause

limitClause :: HeadedParsec Void Text LimitClause

References

limit_clause:
  | LIMIT select_limit_value
  | LIMIT select_limit_value ',' select_offset_value
  | FETCH first_or_next select_fetch_first_value row_or_rows ONLY
  | FETCH first_or_next row_or_rows ONLY

offsetClause :: HeadedParsec Void Text OffsetClause

offsetClauseParams :: HeadedParsec Void Text OffsetClause

selectLimitValue :: HeadedParsec Void Text SelectLimitValue

References

select_limit_value:
  | a_expr
  | ALL

rowOrRows :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s Bool

firstOrNext :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s Bool

selectFetchFirstValue :: HeadedParsec Void Text SelectFetchFirstValue

plusOrMinus :: (Token strm ~ Char, Stream strm, Ord err) => HeadedParsec err strm Bool

For Locking

forLockingClause :: HeadedParsec Void Text ForLockingClause

References

for_locking_clause:
  | for_locking_items
  | FOR READ ONLY
for_locking_items:
  | for_locking_item
  | for_locking_items for_locking_item

forLockingItem :: HeadedParsec Void Text ForLockingItem

References

for_locking_item:
  | for_locking_strength locked_rels_list opt_nowait_or_skip
locked_rels_list:
  | OF qualified_name_list
  | EMPTY
opt_nowait_or_skip:
  | NOWAIT
  | SKIP LOCKED
  | EMPTY

forLockingStrength :: (Token s ~ Char, Tokens s ~ Text, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s ForLockingStrength

References

for_locking_strength:
  | FOR UPDATE
  | FOR NO KEY UPDATE
  | FOR SHARE
  | FOR KEY SHARE

nowaitOrSkip :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s, FoldCase (Tokens s)) => HeadedParsec e s Bool

References & Names

quotedName :: HeadedParsec Void Text Ident

ident :: HeadedParsec Void Text Ident

References

ident_start   [A-Za-z200-377_]
ident_cont    [A-Za-z200-377_0-9$]
identifier    {ident_start}{ident_cont}*

colId :: HeadedParsec Void Text Ident

References

ColId:
  |  IDENT
  |  unreserved_keyword
  |  col_name_keyword

filteredColId :: Foldable t => t Text -> HeadedParsec Void Text Ident

colLabel :: HeadedParsec Void Text Ident

References

ColLabel:
  |  IDENT
  |  unreserved_keyword
  |  col_name_keyword
  |  type_func_name_keyword
  |  reserved_keyword

qualifiedName :: HeadedParsec Void Text QualifiedName

>>> testParser qualifiedName "a.b"
IndirectedQualifiedName (UnquotedIdent "a") (AttrNameIndirectionEl (UnquotedIdent "b") :| [])

>>> testParser qualifiedName "a.-"
...
expecting '*', column label, or white space

References

qualified_name:
  | ColId
  | ColId indirection

columnref :: HeadedParsec Void Text Columnref

filteredColumnref :: [Text] -> HeadedParsec Void Text Columnref

customizedColumnref :: HeadedParsec Void Text Ident -> HeadedParsec Void Text Columnref

anyName :: HeadedParsec Void Text AnyName

filteredAnyName :: Foldable t => t Text -> HeadedParsec Void Text AnyName

customizedAnyName :: HeadedParsec Void Text Ident -> HeadedParsec Void Text AnyName

name :: HeadedParsec Void Text Ident

nameList :: HeadedParsec Void Text (NonEmpty Ident)

cursorName :: HeadedParsec Void Text Ident

funcName :: Parser FuncName

References

func_name:
  | type_function_name
  | ColId indirection

typeFunctionName :: HeadedParsec Void Text Ident

References

type_function_name:
  | IDENT
  | unreserved_keyword
  | type_func_name_keyword

indirection :: HeadedParsec Void Text Indirection

References

indirection:
  | indirection_el
  | indirection indirection_el

indirectionEl :: HeadedParsec Void Text IndirectionEl

References

indirection_el:
  | . attr_name
  | . *
  | '[' a_expr ']'
  | '[' opt_slice_bound : opt_slice_bound ']'
opt_slice_bound:
  | a_expr
  | EMPTY

attrName :: HeadedParsec Void Text Ident

References

attr_name:
  | ColLabel

keywordNameFromSet :: (Tokens strm ~ Text, Token strm ~ Char, Stream strm, Ord err) => HashSet Text -> HeadedParsec err strm Ident

keywordNameByPredicate :: (Tokens strm ~ Text, Token strm ~ Char, Stream strm, Ord err) => (Text -> Bool) -> HeadedParsec err strm Ident

anyKeyword :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s Text

keyword :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => Text -> HeadedParsec e s Text

Expected keyword

keyphrase :: (Token s ~ Char, Tokens s ~ Text, Ord e, Stream s, FoldCase (Tokens s)) => Text -> HeadedParsec e s Text

Consume a keyphrase, ignoring case and types of spaces between words.

Typename

typeList :: Parser TypeList

typename :: HeadedParsec Void Text Typename

arrayBounds :: Integral a => HeadedParsec Void Text (NonEmpty (Maybe a))

simpleTypename :: HeadedParsec Void Text SimpleTypename

genericType :: HeadedParsec Void Text GenericType

attrs :: HeadedParsec Void Text (NonEmpty Ident)

typeModifiers :: HeadedParsec Void Text TypeModifiers

Indexes

indexParams :: HeadedParsec Void Text (NonEmpty IndexElem)

indexElem :: HeadedParsec Void Text IndexElem

indexElemDef :: HeadedParsec Void Text IndexElemDef

collate :: HeadedParsec Void Text AnyName

class_ :: HeadedParsec Void Text AnyName

ascDesc :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s AscDesc

nullsOrder :: (Tokens s ~ Text, Token s ~ Char, Ord e, Stream s) => HeadedParsec e s NullsOrder