PostgreSQL中的PL/pgSQL和SQL是否都与SQL/PSM标准处于同一级别，而不是仅与SQL标准相同? [英] Are PL/pgSQL and SQL in PostgreSQL both at the same level as SQL/PSM standard, instead of as SQL standard only?

问题描述

术语:在下面，PostgreSQL服务器可以理解两种语言

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

• PL/pgSQL;
• 我同时使用"PostgreSQL SQL"和"PostgreSQL中的SQL"来指代同一件事，这是PostgreSQL服务器接收到的命令的默认语言. "PostgreSQL SQL"(即"PostgreSQL中的SQL")与每个SQL标准中的SQL不同.

我发现了以下发现:

PostgreSQL中的

• SQL和PL/pgSQL都可以通过自己的CREATE FUNCTION语句创建函数(实际上是存储过程).我阅读了 PostgreSQL函数中的语言sql和语言plpgsql之间的区别

• SQL in PostgreSQL and PL/pgSQL both can create functions (actually stored procedures) by their own CREATE FUNCTION statements. I read Difference between language sql and language plpgsql in PostgreSQL functions

但是PL/pgSQL允许变量分配，而我找不到PostgreSQL中的SQL允许变量分配.

But PL/pgSQL allows variable assignment, while I don't find that SQL in PostgreSQL allows variable assignment.

由于PostgreSQL SQL具有CREATE FUNCTION并且SQL标准没有存储过程，因此PostgreSQL中的SQL是否与SQL/PSM标准处于同一级别，而不是仅与SQL标准相同?

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?

PostgreSQL和PL/pgSQL中的SQL是否都与SQL/PSM标准处于同一级别?

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

PL/pgSQL和PostgreSQL SQL之间有什么关系?

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

• PL/pgSQL是PostgreSQL中SQL的过程扩展，但是PostgreSQL SQL已经具有CREATE FUNCTION?

PL/pgSQL是否可以替代PostgreSQL中的SQL?可以在PostgreSQL SQL中完成大多数PL/pgSQL的功能吗?

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

如果PostgreSQL中的PL/pgSQL和SQL与SQL/PSM处于同一级别，那么哪个更严格地遵循SQL/PSM标准?

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

谢谢.

更多背景:

我了解到我们必须在PostgreSQL SQL中使用DO命令来指定使用PL/pgSQL ，否则命令仅在PostgreSQL SQL中. 在MySQL中，我不必做类似的事情，而只需编写MySQL命令.因此，我不清楚为什么我们同时拥有PL/pgSQL和PostgreSQL SQL，为什么必须通过DO命令在PostgreSQL SQL中使用PL/pgSQL，而不是仅使用其中一个而又没有在PostgreSQL SQL中混合PL/pgSQL的尴尬.

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.

推荐答案

清除术语:

SQL是一种查询语言，用于选择，更新，删除或创建关系数据库中的数据.它没有程序元素，例如循环(FOR，WHILE)或条件语句( IF，ELSE)或变量或游标.

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确实是一个"SQL语句"，但仅仅是一个包装器"，用于指定由不同于SQL查询引擎"的某些内容执行的代码块. Postgres(与其他DBMS不同)支持多个运行时引擎"，这些引擎可以执行传递给"CREATE FUNCTION"语句的代码块-其中一个工件是该代码实际上是一个字符串，因此CREATE FUNCTION仅看到一个字符串， 没有其他的.

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.

因为SQL没有过程元素，所以不能混合过程代码和SQL代码.如果要运行过程代码，则需要告诉服务器您正在以某种方式切换引擎".这是通过(SQL)DO命令完成的，该命令再次接收一个不知道要怎么处理的字符串，将其发送到服务器，并说这是一段代码，用户在其中声明了引擎". xyz'可以执行"-xyz是PL/pgSQL，Python，Perl或完全不同的东西.

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.

这与以DECLARE开头的Oracle中的匿名PL/SQL块相同-之后的所有内容均由服务器上的其他运行时引擎执行. MySQL没有这种功能.运行过程代码的唯一方法是创建一个过程(或函数)，然后运行该过程.这就是为什么MySQL中没有DO这样的东西.

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.

SQL Server是唯一不能清楚地区分程序代码和普通SQL"的DBMS产品:T-SQL是SQL语言的扩展，允许您在不告知后端的情况下混合使用常规SQL"和程序SQL.代码需要运行不同的引擎(这对于从SQL Server迁移到Postgres或Oracle的人们来说是一个很大的困惑).

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是定义可以嵌入到使用SQL作为查询语言的数据库引擎中的过程元素的标准.我不知道实际上没有实现SQL/PSM的DBMS产品. Postgres的PL/pgSQL，Oracle的PL/SQL，MySQL的过程方言与此相似，但远不符合SQL/PSM标准.我认为最接近SQL/PSM标准的是DB2，也许 HSQLDB

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

PostgreSQL中的SQL"与按照SQL标准的SQL不同.

是的.但是，随后，否 DBMS完全实现了SQL标准-但是Postgres的实现可能是最接近该标准的一种.

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

这篇关于PostgreSQL中的PL/pgSQL和SQL是否都与SQL/PSM标准处于同一级别，而不是仅与SQL标准相同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！