postgresql - PL/pgSQL 列名与变量相同

Question

我是 plpgsql 的新手，我正在尝试创建一个函数来检查表中是否存在某个值，如果不存在则添加一行。

CREATE OR REPLACE FUNCTION hire(
    id_pracownika integer,
    imie character varying,
    nazwisko character varying,
    miasto character varying,
    pensja real)
  RETURNS TEXT AS
$BODY$
DECLARE
wynik TEXT;
sprawdzenie INT;
BEGIN
sprawdzenie = id_pracownika;
IF EXISTS (SELECT id_pracownika FROM pracownicy WHERE id_pracownika=sprawdzenie) THEN
wynik = "JUZ ISTNIEJE";
RETURN wynik;
ELSE
INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
VALUES (id_pracownika,imie,nazwisko,miasto,pensja);
wynik = "OK";
RETURN wynik;   
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

问题是我收到错误提示 id_pracownika 是一个列名和一个变量。

如何在这种上下文中指定“id_pracownika”指的是列名？

Answer 1

假设 id_pracownika 是表的 PRIMARY KEY。或者至少定义了 UNIQUE。 (如果不是 NOT NULL，NULL 是一个极端情况。)

SELECT 或 INSERT

您的函数是“SELECT or INSERT”的另一种实现 - UPSERT 问题的变体，面对并发写入负载，它比看起来更复杂。见:

• Is SELECT or INSERT in a function prone to race conditions?

在 Postgres 9.5 或更高版本中使用 UPSERT

在 Postgres 9.5 或更高版本中使用 UPSERT(INSERT ... ON CONFLICT ...)Details in the Postgres Wiki.这个新语法做了一个干净的工作:

CREATE OR REPLACE FUNCTION hire(
        _id_pracownika integer
      , _imie varchar
      , _nazwisko varchar
      , _miasto varchar
      , _pensja real)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO pracownicy
          ( id_pracownika, imie, nazwisko, miasto, pensja)
   VALUES (_id_pracownika,_imie,_nazwisko,_miasto,_pensja)
   ON     CONFLICT DO NOTHING;

   IF FOUND THEN
      RETURN 'OK';
   ELSE
      RETURN 'JUZ ISTNIEJE';  -- already exists
   END IF;
END
$func$;

关于特殊变量FOUND:

• Why is IS NOT NULL false when checking a row type?

表限定列名称以在必要时消除歧义。 (您也可以在函数参数前加上函数名称前缀，但这很快就会变得尴尬。)
但是 INSERT 的目标列表中的列名可能不是表限定的。无论如何，这些永远不会模棱两可。

最好先避免歧义。有些人(包括我)喜欢在所有函数参数和变量前加上下划线。

如果您确实需要一个列名称作为函数参数名称，避免命名冲突的一种方法是使用 ALIAS在函数里面。 ALIAS 实际有用的罕见情况之一。

或者通过ordinal position引用函数参数:在这种情况下，$1 用于 id_pracownika。

如果所有其他方法都失败了，您可以通过设置 #variable_conflict 来决定优先级。见:

• Naming conflict between function parameter and result of JOIN with USING clause

还有更多:

• UPSERT 中的 RETURNING 子句有些复杂。见:

  • How to use RETURNING with ON CONFLICT in PostgreSQL?

• 字符串文字(文本常量)必须用单引号引起来:'OK'，而不是 "OK" 。见:

  • Insert text with single quotes in PostgreSQL

• 与其他编程语言相比，分配变量的成本相对更高。将分配保持在最低限度以在 plpgsql 中获得最佳性能。尽可能直接在 SQL 语句中执行。

• VOLATILE COST 100 是函数的默认装饰器。无需拼写出来。

在 Postgres 9.4 或更早版本中没有 UPSERT

...
   IF EXISTS (SELECT FROM pracownicy p
             WHERE  p.id_pracownika = hire.id_pracownika) THEN
      RETURN 'JUZ ISTNIEJE';
   ELSE
      INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
      VALUES (hire.id_pracownika,hire.imie,hire.nazwisko,hire.miasto,hire.pensja);
    
      RETURN 'OK';
   END IF;
...

但是 SELECT 和 INSERT 之间存在微小的竞争条件，因此在高并发写入负载下不是防弹的。

在 EXISTS 表达式中，SELECT 列表无关紧要。 SELECT id_pracownika、SELECT 1，甚至 SELECT 1/0 - 都一样。只需使用一个空的 SELECT 列表。只有任何符合条件的行的存在都很重要。见:

• What is easier to read in EXISTS subqueries?