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”是指列名？

- user3653415

---

使用不同的名称来命名（输入）参数和表/列名。如果不这样做，PostgreSQL 怎么知道要使用什么？ - Frank Heikens

文档已经明确指出：应该使用 hire.id_pracownika 引用参数，使用 pracownicy.id_pracownika 引用列（像往常一样）。文档链接 - pozs

你已经将 sprawdzenie = id_pracownika; 赋值了，所以只需在 INSERT INTO pracownicy(id_pracownika..,..,) VALUES(sprawdzenie,..,..) 中使用 sprawdzenie 即可。 - Vivek S.

Answer 1

假设 id_pracownika 是表的主键，或至少定义为 UNIQUE。（如果它不是 NOT NULL，那么 NULL 是一个特殊情况。）

SELECT 或 INSERT

您的函数是“SELECT or INSERT”的另一种实现——这是 UPSERT 问题的变体，在并发写入负载面前比看起来更加复杂。请参见：

• 函数中的 SELECT 或 INSERT 是否容易出现竞争条件？

使用 Postgres 9.5 或更高版本的 UPSERT

在 Postgres 9.5 或更高版本中，使用 UPSERT（INSERT ... ON CONFLICT ...）详情请参阅 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：

• 为什么检查行类型时IS NOT NULL为false？

在必要的情况下，使用表限定列名以消除歧义。（您也可以在函数参数前缀中加上函数名称，但这很快就会变得笨拙。）
但是，在INSERT的目标列表中的列名可能没有表限定。那些从来不会有歧义。

最好先避免模棱两可。一些人（包括我）喜欢用下划线作为所有函数参数和变量的前缀。

如果您确实需要将列名作为函数参数名，避免命名冲突的一种方法是在函数内部使用ALIAS。这是ALIAS实际上有用的少数情况之一。

你可以通过 顺序位置 引用函数参数：$1 在这种情况下表示 id_pracownika。

如果其他方法都失败了，你可以通过设置 #variable_conflict 来确定哪个优先级更高。参见：

• 函数参数与使用 USING 子句连接的结果之间的命名冲突

还有更多内容：

• UPSERT中的RETURNING子句有复杂性。请参见：

  • 如何在PostgreSQL中使用ON CONFLICT和RETURNING？

• 字符串字面量（文本常量）必须用单引号括起来：'OK'，而不是"OK"。请参见：

  • 在PostgreSQL中插入带有单引号的文本

• 相对于其他编程语言，赋值变量的成本更高。为了获得最佳性能，请尽可能直接在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列表即可。只有任何符合条件的行的存在才是重要的。请参见：

• EXISTS子查询中哪个更易读？

- Erwin Brandstetter

---

我认为第一个参数的名称不正确，应该是hire(sprawdzenie integer, etc…。 - GG.

关于最后一部分，只有在pracownicy.id_pracownika上有一个UNIQUE约束才能起作用，对吗？（无论如何，这是一个很好的建议，我不知道ON CONFLICT）。编辑：哦，我想你假设它是PRIMARY KEY，所以它是UNIQUE。 - GG.

@GG：感谢您指出。我已经修复/澄清了两个问题。顺便把现代解决方案移到了顶部。 - Erwin Brandstetter

@ErwinBrandstetter https://dev59.com/jm0NtIcB2Jgan1znKXwo 能帮我解决这个问题吗？ - jian

@Mark：我意识到我之前的更新引入了语法错误，现在已经修复。感谢你提醒我！ - Erwin Brandstetter

Answer 2

这是我测试过的一个例子，其中我使用EXECUTE来运行SELECT并将其结果放入游标中，使用动态列名。 1. 创建表：

create table people (
  nickname varchar(9),
  name varchar(12),
  second_name varchar(12),
  country varchar(30)
  );

创建函数：2. Create the function:

CREATE OR REPLACE FUNCTION fun_find_people (col_name text, col_value varchar)
RETURNS void AS
$BODY$
DECLARE
    local_cursor_p refcursor;
    row_from_people RECORD;

BEGIN
    open local_cursor_p FOR
        EXECUTE 'select * from people where '|| col_name || ' LIKE ''' || col_value || '%'' ';

    raise notice 'col_name: %',col_name;
    raise notice 'col_value: %',col_value;

    LOOP
        FETCH local_cursor_p INTO row_from_people; EXIT WHEN NOT FOUND;

        raise notice 'row_from_people.nickname: %',  row_from_people.nickname ;
        raise notice 'row_from_people.name: %', row_from_people.name ;
        raise notice 'row_from_people.country: %', row_from_people.country;
    END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql'

3. 运行该函数 select fun_find_people('name', 'Cristian'); select fun_find_people('country', 'Chile');

- Cristian

Answer 3

受 Erwin Brandstetter 的回答启发。

CREATE OR REPLACE FUNCTION test_upsert(
        _parent_id int, 
        _some_text text)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE a text;
BEGIN
   INSERT INTO parent_tree (parent_id, some_text)
   VALUES (_parent_id,_some_text)
   ON     CONFLICT DO NOTHING
   RETURNING 'ok' into a;
   return a;

   IF NOT FOUND THEN
        return 'JUZ ISTNIEJE';
   END IF;
END
$func$;

1. 请遵循Erwin的答案。我将使用变量a来保存返回类型的文本。
2. 如果发生冲突，则不执行任何操作，函数将不会返回任何内容。例如，已经有parent_id = 10，那么结果将如下所示：

test_upsert
------------

(1 row)

不确定以下代码的用法：

IF NOT FOUND THEN
     return 'JUZ ISTNIEJE';
END IF;

- jian