PostgreSQL 函数中语言 sql 和语言 plpgsql 的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24755468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between language sql and language plpgsql in PostgreSQL functions
提问by Erwin Brandstetter
Am very new in Database developmentso I have some doubts regarding my following example:
在数据库开发方面非常新，所以我对以下示例有一些疑问：
Function f1() - language sql
函数 f1() -语言 sql
create or replace function f1(istr varchar) returns text as $$
select 'hello! '::varchar || istr;
$$ language sql;
Function f2() - language plpgsql
函数 f2() -语言 plpgsql
create or replace function f2(istr varchar)
returns text as $$
begin select 'hello! '::varchar || istr; end;
$$ language plpgsql;
Both functionscan be called like
select f1('world')orselect f2('world').If I call
select f1('world')the outputwill be:`hello! world`And outputfor
select f2('world'):ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function f11(character varying) line 2 at SQL statement ******Error ******
I wish to know the difference and in which situations I should use
language sqlorlanguage plpgsql.
这两个函数都可以调用 like
select f1('world')或select f2('world')。如果我打电话
select f1('world')的输出将是：`hello! world`并输出为
select f2('world')：错误：查询没有结果数据的目的地 提示：如果要放弃 SELECT 的结果，请改用 PERFORM。上下文：PL/pgSQL 函数 f11（字符变化）第 2 行在 SQL 语句 ******错误******
我想知道区别以及我应该在哪些情况下使用
language sql或language plpgsql。
Any useful link or answers regarding functions will much appreciated.
任何有关功能的有用链接或答案将不胜感激。
回答by Erwin Brandstetter
SQL functions
SQL 函数
are the better choice:
是更好的选择：
For simple scalar queries. Not much to plan, better save any overhead.
For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below.
If they are typically called in the context of bigger queries and are simple enough to be inlined.
For lack of experiencewith any procedural language like PL/pgSQL. Many know SQL well and that's about all you need for SQL functions. Few can say the same about PL/pgSQL. (Though it's rather simple.)
A bit shorter code. No block overhead.
对于简单的标量查询。没有太多计划，更好地节省任何开销。
对于每个会话的单个（或很少）调用。通过 PL/pgSQL 必须提供的准备好的语句从计划缓存中没有任何好处。见下文。
如果它们通常在更大查询的上下文中被调用并且足够简单以进行内联。
由于缺乏使用任何过程语言（如 PL/pgSQL）的经验。许多人都非常了解 SQL，这就是 SQL 函数所需的全部内容。很少有人能对 PL/pgSQL 说同样的话。（虽然这很简单。）
代码短一点。没有块开销。
PL/pgSQL functions
PL/pgSQL 函数
are the better choice:
是更好的选择：
When you need any procedural elementsor variablesthat are not available in SQL functions, obviously.
For any kind of dynamic SQL, where you build and
EXECUTEstatements dynamically. Special care is needed to avoid SQL injection. More details:When you have computationsthat can be reusedin several places and a CTE can't be stretched for the purpose. In an SQL function you don't have variables and would be forced to compute repeatedly or write to a table. This related answer on dba.SE has side-by-side code examplesfor solving the same problem using an SQL function / a plpgsql function / a query with CTEs:
Assignments are somewhat more expensive than in other procedural languages. Adapt a programming style that doesn't use more assignments than necessary.
When a function cannot be inlined and is called repeatedly. Unlike with SQL functions, query plans can be cachedfor all SQL statements inside a PL/pgSQL functions; they are treated like prepared statements, the plan is cached for repeated calls within the same session (if Postgres expects the cached (generic) plan to perform better than re-planning every time. That's the reason why PL/pgSQL functions are typically fasterafter the first couple of calls in such cases.
Here is a thread on pgsql-performance discussing some of these items:
When you need to trap errors.
For trigger procedures(which are just functions, too).
When including DDL statements changing objects or altering system catalogs in any way relevant to subsequent commands - because all statements in SQL functions are parsed at once while PL/pgSQL functions plan and execute each statement sequentially (like a prepared statement). See:
显然，当您需要SQL 函数中不可用的任何过程元素或变量时。
对于任何类型的动态 SQL，您可以在其中
EXECUTE动态构建和声明。需要特别注意避免 SQL 注入。更多细节：当您有可以在多个地方重复使用的计算并且 CTE 不能为此目的而扩展时。在 SQL 函数中，您没有变量，将被迫重复计算或写入表。dba.SE 上的这个相关答案有并排的代码示例，用于使用 SQL 函数/plpgsql 函数/带有 CTE 的查询解决相同的问题：
赋值比其他过程语言要贵一些。调整不使用过多分配的编程风格。
当一个函数不能被内联并被重复调用时。与 SQL 函数不同，查询计划可以为 PL/pgSQL 函数内的所有 SQL 语句缓存；它们被视为准备好的语句，计划被缓存以用于同一会话中的重复调用（如果 Postgres 期望缓存的（通用）计划比每次重新计划执行得更好。这就是为什么 PL/pgSQL 函数通常在之后更快的原因在这种情况下的前几个电话。
这是一个关于 pgsql-performance 的线程，讨论了其中的一些项目：
当您需要捕获错误时。
对于触发器过程（也只是函数）。
当包含 DDL 语句以与后续命令相关的任何方式更改对象或更改系统目录时 - 因为 SQL 函数中的所有语句都被一次解析，而 PL/pgSQL 函数则按顺序计划和执行每个语句（如准备好的语句）。看：
Also consider:
还要考虑：
To actually returnfrom a PL/pgSQL function, you could write:
要真正从 PL/pgSQL 函数返回，您可以编写：
CREATE FUNCTION f2(istr varchar)
RETURNS text AS
$func$
BEGIN
RETURN 'hello! '; -- defaults to type text anyway
END
$func$ LANGUAGE plpgsql;
There are other ways:
还有其他方法：
回答by Craig Ringer
PL/PgSQL is a PostgreSQL-specific procedural language based on SQL. It has loops, variables, error/exception handling, etc. Not all SQL is valid PL/PgSQL - as you discovered, for example, you can't use SELECTwithout INTOor RETURN QUERY. PL/PgSQL may also be used in DOblocks for one-shot procedures.
PL/PgSQL 是一种基于 SQL 的特定于 PostgreSQL 的过程语言。它具有循环、变量、错误/异常处理等。并非所有 SQL 都是有效的 PL/PgSQL - 例如，正如您所发现的，SELECT没有INTO或就不能使用RETURN QUERY。PL/PgSQL 也可以用于DO一次性过程的块中。
sqlfunctionscan only use pure SQL, but they're often more efficient and they're simpler to write because you don't need a BEGIN ... END;block, etc. SQL functions may be inlined, which is not true for PL/PgSQL.
sql函数只能使用纯 SQL，但它们通常效率更高，编写起来更简单，因为您不需要BEGIN ... END;块等。SQL 函数可能是内联的，这对于 PL/PgSQL 而言并非如此。
People often use PL/PgSQL where plain SQL would be sufficient, because they're used to thinking procedurally. In most cases when you think you need PL/PgSQL you probably actually don't. Recursive CTEs, lateral queries, etc generally meet most needs.
人们经常使用 PL/PgSQL，而普通 SQL 就足够了，因为他们习惯于按程序进行思考。在大多数情况下，当您认为需要 PL/PgSQL 时，您实际上可能不需要。递归 CTE、横向查询等通常可以满足大多数需求。
For more info ... see the manual.
有关更多信息……请参阅手册。
回答by ZORRO_BLANCO
just make the select query you wrote inside the function as the returned value:
只需将您在函数中编写的选择查询作为返回值：
create or replace function f2(istr varchar)
returns text as $$
begin return(select 'hello! '::varchar || istr); end;
$$ language plpgsql;
