The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Pyrrho SQL syntax

SQL Functions

FunctionCall = NumericValueFunction | StringValueFunction | DateTimeFunction | SetFunction | XMLFunction | UserFunctionCall | MethodCall .

NumericValueFunction = AbsoluteValue | Avg | Cast | Ceiling | Coalesce | Count | Exponential | Extract | Floor | Grouping | LengthExpression | Maximum | Minimum | Modulus | NaturalLogarithm | Nullif | Position | PowerFunction | RowNumber | Schema | SquareRoot | Sum .

AbsoluteValue = ABS '(' Value ')' .

Avg = AVG '(' Value ')' FuncOpt .

FuncOpt = [FILTER '(' WHERE SearchCondition ')'] [ OVER WindowSpec ] .

OVER makes a window function, and can only be used in the select list of a QuerySpecification or SelectSingle, or the order by clause of a "simple table query".

WindowSpec = Window_id | '(' WindowDetails ')' .

Cast = ( CAST | XMLCAST ) '(' Value AS Type ')' .

Ceiling = (CEIL|CEILING) '(' Value ')' .

Coalesce = COALESCE '(' Value {',' Value } ')' .

Count = COUNT '('  '*' ')'
| COUNT '(' [DISTINCT|ALL] Value ) ')'  FuncOpt .

Exponential = EXP '(' Value ')' .

Extract = EXTRACT '(' ExtractField FROM Value ')' .

ExtractField = YEAR | MONTH | DAY | HOUR | MINUTE | SECOND.

Floor = FLOOR '(' Value ')' .

Grouping = GROUPING '(' ColumnRef { ',' ColumnRef } ')' .

Last = LAST ['('ColumnRef')' OVER WindowSpec ] .

LengthExpression = (CHAR_LENGTH|CHARACTER_LENGTH|OCTET_LENGTH) '(' Value ')' .

DateTimeFunction = CURRENT_DATE | CURRENT_TIME | LOCALTIME | CURRENT_TIMESTAMP | LOCALTIMESTAMP .

Nullif = NULLIF '(' Value ',' Value ')' .

Maximum = MAX '(' Value ')'  FuncOpt .

Minimum = MIN '(' Value ')'  FuncOpt .

Modulus = MOD '(' Value ',' Value ')' .

NaturalLogarithm = LN '(' Value ')' .

Next = NEXT ['('ColumnRef')' OVER WindowSpec ].

WithinGroup = WITHIN GROUP '(' OrderByClause ')' .

Position = POSITION ['('Value IN Value ')' ].

POSITION without parameters gives a position in Pyrrho's log file.

PowerFunction = POWER '(' Value ',' Value ')' .

RowNumber = ROW_NUMBER '(' ')' OVER WindowSpec .

Schema = SCHEMA '(' ObjectName [COLUMN id] ')' .

Added for Pyrrho for help with forensic analysis of a database: gives the log position of the most recent schema change for the given object. If the object is a table, then the optional column name can be useful. (The schema change found may well be for a different role where the objects may have different names.)

SquareRoot = SQRT '(' Value ')' .

Sum = SUM '(' Value ')' .

StringValueFunction = Substring | XmlAgg .

Substring = SUBSTRING '(' Value FROM Value [ FOR Value ] ')' .

Trim = TRIM '(' [[LEADING|TRAILING|BOTH] [character] FROM] Value ')' .

XmlAgg = XMLAGG '(' Value  OrderByClause ')' .

SetFunction = Cardinality | Element | Set .

Cardinality = CARDINALITY '(' Value ')' .

Element = ELEMENT '(' Value ')' .

Set = SET '(' Value ')' .