0

Terminology: In the following, there are two kinds of languages understood by PostgreSQL server

  • PL/pgSQL;
  • I use both "PostgreSQL SQL" and "SQL in PostgreSQL" to refer to the same thing, the default language of the commands received by PostgreSQL server. "PostgreSQL SQL" i.e. "SQL in PostgreSQL" is not the same thing as SQL per SQL standard.

I have found the following observations:

Since PostgreSQL SQL has CREATE FUNCTION and SQL standard doesn't have stored procedures, is SQL in PostgreSQL at the same level as SQL/PSM standard, instead of as SQL standard only?

Are SQL in PostgreSQL and PL/pgSQL both at the same level as SQL/PSM standard?

What is the relation betw PL/pgSQL and PostgreSQL SQL?

  • Is PL/pgSQL a procedural extension to SQL in PostgreSQL, but PostgreSQL SQL already has CREATE FUNCTION?

  • Is PL/pgSQL an alternative to SQL in PostgreSQL? Can most of what PL/pgSQL can do also be done in PostgreSQL SQL?

If both PL/pgSQL and SQL in PostgreSQL are at the same level as SQL/PSM, which one more closely follows SQL/PSM standard?

Thanks.

More background:

I learned that we have to use DO command in PostgreSQL SQL to specify using PL/pgSQL, otherwise the commands are just in PostgreSQL SQL. In MySQL, I never have to do similar things, but just write MySQL commands. So I am not clear why we have both PL/pgSQL and PostgreSQL SQL, and have to use PL/pgSQL in PostgreSQL SQL via DO command, instead of just using one of them without the awkwardness of mixing PL/pgSQL in PostgreSQL SQL.

Tim
  • 1
  • 141
  • 372
  • 590
  • "*can what can be done in PL/pgSQL also be done in SQL in PostgreSQL*" no - SQL does not have `IF`, loops, variables or other procedural language elements. –  Jun 14 '18 at 13:45
  • You can't write procedural code (loops, `if` statements, cursors etc) in MySQL outside of a stored procedure or function so I don't see how this relates to the DO statement - which is a way to write procedural code _without_ a stored function (or procedure) –  Jun 14 '18 at 14:00
  • 1
    "*I am not clear why we have both PL/pgSQL SQL*" - because SQL (the query language) has no procedural elements - simple as that. –  Jun 14 '18 at 14:01
  • Thanks. Re your last comment. I am not talking about SQL standard, but PostgreSQL SQL. PostgreSQL SQL has its own `CREATE FUNCTION`. – Tim Jun 14 '18 at 14:03
  • But SQL **has no procedural elements** –  Jun 14 '18 at 14:04
  • Thanks. What is difference betwee stored procedural and "procedural elements"? – Tim Jun 14 '18 at 14:05

2 Answers2

3

To clear up the terminology:

SQL is a query language that is used to select, update, delete or create data in a relational database. It has no procedural elements like loops (FOR, WHILE) or conditional statements (IF, ELSE) or variables or cursors.

CREATE FUNCTION is indeed a "SQL statement" but is is merely a "wrapper" to specify a block of code that is executed by something different than the SQL query "engine". Postgres (unlike other DBMS) supports multiple "runtime engines" that can execute the block of code that was passed to the "CREATE FUNCTION" statement - one artifact of that is that the code is actually a string so CREATE FUNCTION only sees a string, nothing else.

Because SQL has no procedural elements, you can't mix procedural code and SQL code. If you want to run procedural code you need to tell the server that you are switching "engines" somehow. This is done through the (SQL) DO command which again takes a string that it doesn't know what to do with, sends it to the server and says "here is a piece of code where the user claimed that the engine 'xyz' can execute" - xyz is either PL/pgSQL, Python, Perl or something entirely different.

This is the same as an anonymous PL/SQL block in Oracle that you start with DECLARE - everything after that is executed by a different runtime engine on the server. MySQL has no such feature. The only way to run procedural code is to create a procedure (or function), then run that. That's why there is no such thing as DO in MySQL.

The only DBMS product which does not clearly distinguish between procedural code and "plain SQL" is SQL Server: T-SQL is an extension to the SQL language which allows you to mix "regular SQL" and procedural SQL without telling the backend that the code needs a different engine to run (which is a source of great confusion for people migrating from SQL Server to Postgres or Oracle).

SQL/PSM is a standard that defines procedural elements that can be embedded into a database engine that uses SQL as its query language. I know of no DBMS product that actually implements SQL/PSM. Postgres' PL/pgSQL, Oracle's PL/SQL, MySQL's procedural dialect are somewhat similar to that, but far from being compliant with the SQL/PSM standard. I think the closest to the SQL/PSM standard is DB2 and maybe HSQLDB

SQL in PostgreSQL" is not the same thing as SQL per SQL standard.

That is true. But then, no DBMS fully implements the SQL standard - but Postgres' implementation is probably one closest to the standard.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks. "CREATE FUNCTION is indeed a 'SQL statement'", do you mean **the SQL standard** has `CREATE FUNCTION` or similar? – Tim Jun 14 '18 at 16:07
  • Thanks again. What does the code following `CREATE FUNCTION` being a string imply: https://stackoverflow.com/questions/50867972/what-does-the-code-following-create-function-being-a-string-imply – Tim Jun 15 '18 at 01:18
  • @Tim: in a nutshell: the code that runs `create function` can't verify that the code in the String is correct - it must be handed over for verification to the engine that later runs _that_ code. –  Jun 15 '18 at 05:25
  • Thanks. Is that considered as dynamic SQL? Does it prevent or introduce SQL injection risk, compared to dynamic SQL? – Tim Jun 15 '18 at 11:20
2

SQL is a query language, while PL/pgSQL is a procedural language (it has statements like LOOP and IF).

Procedural languages are used to write functions; the body of a function can be written in PL/pgSQL.

CREATE FUNCTION is an SQL statement that defines a function object in the database. Such a function can be used in SQL expressions.

SQL/PSM is the part of the standard that defines the stored procedure language, so SQL/PSM would be applicable to PL/pgSQL, but not to SQL.

However, PL/pgSQL does not follow the SQL/PSM standard.

Nobody tells you that you have to use PL/pgSQL, and indeed it makes your application more portable (on the database end) not to use it. But SQL and procedural languages are something different (in PostgreSQL as well as in MySQL), and you may find that both have their uses.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263