Hello!
Inspired by Simon Riggs' keynote talk at PGCounf.eu 2023 sharing list
of ideas for PostgreSQL
(https://riggs.business/blog/f/postgresql-todo-2023), I have crafted a
quick patch to do SQL syntax validation.
It is also heavily inspired by the "ruby -c" command, useful to check
syntax of Ruby programs without executing them.
For now, to keep it simple and to open discussion, I have added new
"--syntax" option into "postgres" command, since it is currently the
only one using needed parser dependency (at least per my
understanding). I tried to add this into psql or separate pg_syntax
commands, but parser is not exposed in "postgres_fe.h" and including
backend into those tools would not make most likely sense. Also syntax
could vary per backend, it makes sense to keep it in there.
It expects input on STDIN, prints out error if any and prints out
summary message (Valid SQL/Invalid SQL). On valid input it exits with
0 (success), otherwise it exits with 1 (error).
Example usage:
$ echo "SELECT 1" | src/backend/postgres --syntax
Valid SQL
$ echo "SELECT 1abc" | src/backend/postgres --syntax
ERROR: trailing junk after numeric literal at or near "1a" at character 8
Invalid SQL
$ cat ../src/test/regress/sql/alter_operator.sql | src/backend/postgres --syntax
Valid SQL
$ cat ../src/test/regress/sql/advisory_lock.sql | src/backend/postgres --syntax
ERROR: syntax error at or near "\" at character 99
Invalid SQL
This could be useful for manual script checks, automated script checks
and code editor integrations.
Notice it just parses the SQL, it doesn't detect any "runtime"
problems like unexisting table names, etc.
I have various ideas around this (like exposing similar functionality
directly in SQL using custom function like pg_check_syntax), but I
would like to get some feedback first.
What do you think?
enhnace
PS: I wasn't able to find any automated tests for "postgres" command
to enhance with, are there any?
PS2: Patch could be found at https://github.com/simi/postgres/pull/8 as well.