Fill out stdlib

[max-sixty]

We have a stdlib, written by @qorrect , which provides some standard functions by using s-strings.

We could fill this out with some more standard functions — e.g. EXP, MOD, string functions, etc.

Possibly there is even a definition of these somewhere?

[aljazerzen]

The problem here is that different dialects have different functions (see datetime and such). I tried to find SQL standard (ISO/IEC 9075) in the parts of the standard I manged to find there was no mentions of functions like CONCAT. This leads me to think there is no explicit standard...

@qorrect @max-sixty
Does anyone know if SQL has a list of standard functions defined somewhere?

For dialect specific stdlib, we can extract list of all function from docs (or even source code?), as said in #301.

[max-sixty]

The best reference I can find is http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html, but it doesn't strictly give ANSI functions.

There's also http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt, but that only seems to give 5 functions!

         Function

         Specify a value derived by the application of a function to an
         argument.

         Format

         <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>


         <set function type> ::=
              AVG | MAX | MIN | SUM | COUNT

         <set quantifier> ::= DISTINCT | ALL

So possibly there isn't a way of doing this systematically and we should just build out the functions that people want, maybe with something like Postgres' manual as a guide...

[max-sixty]

Ah, it's possible to buy (?!): https://www.iso.org/standard/63555.html

There are also lists of "Reserved words": https://en.wikipedia.org/wiki/SQL_reserved_words, but that's a large superset of functions

[aljazerzen]

The two links you've sent are quite old and they don't seem exhaustive, but may serve as a checklist of things to add.

9075 has 11 parts (9075-1, 9075-2, 9075-3, ...). I don't know which (if any) contains a list of functions, since I've only managed to get my hands on 9075-1.

Possibly, we may be able to find it in genesis library, but I cannot comment on that :D

It seems like scraping docs of postres would be the most efficient way of doing this...

[max-sixty]

I purchased the ISO Spec (and sent it on Discord in case you're interested) — but it doesn't even have functions! And I couldn't get from the titles of the other 14.

What's the genesis library?

Agree that postgres is probably the best path1

[max-sixty]

Something that might be clever or might be a recipe for confusion:

Translate every foo X Y into FOO(X, Y)

On the upside, it basically gives us every stdlib function for free. On the downside, it's difficult to rollback (it's very pervasive), and it could lead to quite confusing errors.

[aljazerzen]

Oh no, that would cause a lot of problems I think... An interesting idea, but for now, I believe it is safer refer to s-string for anything that's not yet in stdlib...

[vanillajonathan]

Furthmore the stdlib is not documented. #1761