Is a sql script interpretable solely by a RDBMS such as PostgreSQL server, or by both the server and a client such as psql? Can the server accept a SQL script file, or only a client can?
My question comes from learning that in psql, \i can be used to read and execute a sql script. But if a sql script is acceptable by a Postgresql server, how can I provide the script to the server?
When writing a sql script, how can I write a comment? Is # still used for signaling comment?
Do I need to provide a shebang? If yes, is it a good idea to have a shebang than not?
Thanks.
I am trying to place https://stackoverflow.com/a/771880/156458 into a sql script, so that I can reuse it.
Like many SQL shells, psql has a basic understanding of Postgres syntax. This allows it to identify the end of a statement and forward that to the server for execution. Many clients need to understand the syntax for other reasons as well, like syntax highlighting.
The default statement terminator is ; which can appear within strings. This means a basic parser is necessary to avoid sending incomplete statements. This terminator can also change so the client needs to keep track of what the current terminator or "delimiter" is.
There's really no such thing as a stand-alone SQL script which can be executed directly like you might for a scripting language. You should have a minimal wrapper that helps connect to the server properly, authenticate, and then send load in the SQL either by using redirection (e.g. do-sql < test.sql) or features like the \i import tool.