在 PostgreSQL 的 PL/pgSQL 中开发时的一些技巧

当您在 PostgreSQL 的PL/pgSQL中开发函数或过程时，有一些要点需要考虑，这会让您的生活更轻松。这篇文章与控制结构无关，也与一般开发无关。这更多的是关于需要知道的小事情，特别是如果你刚开始使用 PostgreSQL 和 PL/pgSQL。

考虑这个功能：

postgres=# create or replace function f1() returns void as
$$
begin
  select * from t1;
end; $$ language plpgsql;
CREATE FUNCTION

PostgreSQL 将毫无问题地创建该函数，但是一旦您调用该函数，您就会注意到出现错误，因为表“t1”不存在：

postgres=# select f1();
ERROR:  relation "t1" does not exist
LINE 1: select * from t1
                      ^
QUERY:  select * from t1
CONTEXT:  PL/pgSQL function f1() line 3 at SQL statement

这意味着，PostgreSQL 不会检查函数体中引用的对象在创建函数时是否确实存在。但是，它会检查您要返回的数据类型是否确实存在：

postgres=# create or replace function f2() returns my_type as
$$
begin
  select * from t1;
end; $$ language plpgsql;
ERROR:  type "my_type" does not exist

另一点需要考虑的是，您可以启用额外的编译时和运行时检查。让我们看一下以下函数：

postgres=# create or replace function f3(p1 int) returns int as
$$
declare
  p1 int := 1;
begin
  return p1;
end; $$ language plpgsql;
CREATE FUNCTION

如果我们这样调用函数：

postgres=# select * from f3(2);

……它会返回什么，一两个？进入函数的参数与函数中的变量同名。

postgres=# select * from f3(2);
 f3 
----
  1
(1 row)

这称为变量阴影，您可以告诉 PostgreSQL 对此进行检查：

postgres=# set plpgsql.extra_warnings to 'shadowed_variables';
SET
postgres=# create or replace function f3(p1 int) returns int as
$$
declare
  p1 int := 1;
begin
  return p1;
end; $$ language plpgsql;
WARNING:  variable "p1" shadows a previously defined variable
LINE 4:   p1 int := 1;
          ^
CREATE FUNCTION
postgres=# 

让我们看另一个例子：

postgres=# create table t ( a int, b int );
CREATE TABLE
postgres=# insert into t values (1,1);
INSERT 0 1
postgres=# create function f4() returns void as
$$
declare
  v1 int;
  v2 int;
begin
  select a,b
    into v1,v2
    from t;
end; $$ language plpgsql;
CREATE FUNCTION

目前执行此操作不是问题，因为该表恰好包含一行：

postgres=# select * from f4();
 f4 
----
  
(1 row)

但是，如果该表中有多于一行，会发生什么情况？

postgres=# insert into t values (2,2);
INSERT 0 1
postgres=# select * from f4();
 f4 
----
  
(1 row)

PostgreSQL 仍将执行该函数，但您如何知道您得到的是哪一行？如果你有一个“into”语句并且可能会返回多行，这可能是错误的，你可以要求 PostgreSQL 警告你：

postgres=# set plpgsql.extra_warnings to 'too_many_rows';
SET
postgres=# select * from f4();
WARNING:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
 f4 
----
  
(1 row)
 
postgres=# 

今天的最后一个例子：考虑一下：

postgres=# create or replace function f5() returns void as
$$
declare
  v1 int;
  v2 int;
begin
  select a,b,'aaa'
    into v1,v2
    from t;
end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select f5();
WARNING:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
 f5 
----
  
(1 row)

我们仍然会收到之前的警告，但是我们选择的第三个值会发生什么变化？PostgreSQL 将忽略它，但您也可以为此启用警告：

postgres=# set plpgsql.extra_warnings to 'strict_multi_assignment';
SET
postgres=# select f5();
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
 f5 
----
  
(1 row)
 
postgres=# 

也可以组合这些标志：

postgres=# set plpgsql.extra_warnings to 'strict_multi_assignment','too_many_rows','shadowed_variables';
SET

至少对于您花在开发功能或过程上的时间，您应该启用它们。

原文标题：Some tips when developing in PostgreSQL’s PL/pgSQL

原文作者：Daniel Westermann

原文链接：https://www.dbi-services.com/blog/some-tips-when-developing-in-postgresqls-pl-pgsql/