1

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.

Tim
  • 1
  • 141
  • 372
  • 590
  • comment in SQL is `/* */` or `--` (multiline and single line) – Lukasz Szozda Jun 15 '18 at 14:59
  • To put it simply: the SQL client (`psql`) reads the file into memory and sends it to the server, just as if you have typed it. And `#` is **not** a valid comment character in SQL. And a "shebang" is something specific to a Unix (or Linux) `shell` and has nothing to do with SQL scripts. –  Jun 15 '18 at 15:04
  • @a_horse_with_no_name does psql pass the content of a sql script to the server,without modifying it? – Tim Jun 15 '18 at 15:07
  • 1
    You can picture it like that, yes –  Jun 15 '18 at 15:08
  • @a_horse_with_no_name Can the server accept a SQL script file, or only a client can? – Tim Jun 15 '18 at 15:21
  • 1
    Postgres has a [fantastic manual](https://www.postgresql.org/docs/10/static/index.html) that can answer a lot of your syntax questions. – tadman Jun 15 '18 at 15:27
  • 1
    @tadman thanks. I am reading it, but not familiar enough about the terminology to find useful info sometimes – Tim Jun 15 '18 at 15:30

1 Answers1

1

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Thanks. So a sql script can be accepted only by client not by server? – Tim Jun 15 '18 at 15:32
  • Is `do-sql` a client alternative to `psql`? – Tim Jun 15 '18 at 15:33
  • "SQL script" is a term you really shouldn't use as it's not really a thing. "SQL snippet" is more appropriate, as that refers to some SQL statements in a file, nothing more, and implies that they need another tool in order to do something. Scripts, by comparison, are something you can directly execute. They can't be executed without both a client *and* a server that are connected together properly. Anything that can connect to your server and supply SQL commands is considered a client. This includes things like a Postgres driver as used in various programming languages. – tadman Jun 15 '18 at 15:33
  • `do-sql` is just an example of a wrapper script you might write. It would consist of something like `psql -u myname ...` and a bunch of other options to ensure it connects properly to the right host, etc. – tadman Jun 15 '18 at 15:34
  • Thanks again. (1) Is the terminator of SQL commands, whether it is a new line or something else, not understandable by the server but by the client only? so a SQL snippet file must be provided to a client not to a server? (2) Does `psql` only accept SQL commands from stdin not from a SQL snippet file? Is it correct that `mysql` also behaves the same a `psql` in this aspect? – Tim Jun 15 '18 at 15:42
  • Generally, but not always, the client will identify statements using the delimiter and forward those to the server using the proper binary protocol. In theory the client can use any delimiter it wants, newline or otherwise, but convention holds that it's typically `;` and can be changed as necessary. Since multi-line statements are common it's rarely newline. – tadman Jun 15 '18 at 15:44
  • Since the server only speaks [Posgres protocol](https://www.postgresql.org/docs/current/static/protocol.html) and not raw SQL you need to use a client of some kind. `psql` is the one provided with the server but there are many others. They differ in features, but most can handle input either pasted in, or loaded from a file. In both cases it's just text. – tadman Jun 15 '18 at 15:45
  • (2) What I meant is that `bash -c` can be used to specify a shell script at command line when invoking `bash`, while does `psql` have such feature (option) or only accept a script in stdin via its metacommand `\i`? – Tim Jun 15 '18 at 15:50
  • If you're curious about the capabilities of `psql` it's [fully documented](https://www.postgresql.org/docs/current/static/app-psql.html). – tadman Jun 15 '18 at 15:52
  • Yes, I have searched it though not read every single word. I did't find an option for `psql` to accept a SQL script file when invoking `psql` from shell – Tim Jun 15 '18 at 15:52
  • It takes input like any command-line program. `psql ... < my.sql` where `my.sql` is your SQL file. This file could consist of a bunch of `\i otherfile.sql` directives, too. – tadman Jun 15 '18 at 15:53
  • Thanks. redirecton is another way. That is still accept input from stdin. The shell redirects stdin to `my.sql`. Unlike `bash -c` though. – Tim Jun 15 '18 at 15:54
  • "Since the server only speaks Posgres protocol and not raw SQL you need to use a client of some kind. " For example, SQL and PL/pgSQL are both server-side languages, so does "server-side" mean they are acceptable and understandable directly by the server? Or does your comment mean that they are not in Posgres protocol , so the server can't understand SQL and PL/pgSQL commands? Is the job of client psql to convert SQL and PL/pgSQL commands to a message in Posgres protocol, and then send the message to the server? – Tim Jun 15 '18 at 16:14
  • If you read up on the Postgres protocol, which is itself separate if related to the SQL command set, it just defines how to transport commands, data, and result sets over a connection. The client has a rudimentary understanding of SQL, just enough to get by, while the server has a complete one in order to actually do something useful. The client "frames" the commands using the protocol and interprets the responses. – tadman Jun 15 '18 at 16:52
  • This is not unlike how HTML and HTTP are related, yet different. – tadman Jun 15 '18 at 16:53
  • 1
    Thanks. I found that `psql -f` seems to be the one which accept a sql script file. Not sure about `mysql` though. cheers. Made a mistake earlier, `bash -c` is for accepting bash commands as inline script i.e. command line args – Tim Jun 15 '18 at 16:56