postgresql - 从 INSERT 或 SELECT 获取 id

Question

我有这个函数，可以在 city 表中插入一行，而不重复。它返回插入行的 ID:

CREATE OR REPLACE FUNCTION public.insert_city(
character varying,
character varying,
character varying,
character varying,
character varying,
character varying)
RETURNS integer AS
$BODY$
DECLARE
name_city1 ALIAS FOR $1;
country1 ALIAS FOR $2;
province1 ALIAS FOR $3;
region1 ALIAS FOR $4;
cap1 ALIAS FOR $5;
nationality1 ALIAS FOR $6;
id_city1 integer;
BEGIN
   INSERT INTO city (name_city, country, province, region, cap, nationality) 
   SELECT name_city1, country1, province1, region1, cap1, nationality1
WHERE NOT EXISTS (SELECT id_city FROM city WHERE name_city = name_city1)
RETURNING id_city INTO id_city1;

-- xxx

END;
$BODY$
LANGUAGE plpgsql VOLATILE;

xxx 标记了我需要这样的东西的地方:

IF is_number(id_city1) THEN
    RETURN id_city1;
ELSE
RETURN query select id_city from city where name_city=name_city1;
END IF;

如果第一个查询没有插入新行并且我没有从中获得 id_city，我想执行第二个查询以选择现有的 id_city .

我该怎么做？

Answer 1

您的功能可以进一步简化。更重要的是，您可以修复内置的竞争条件:

CREATE OR REPLACE FUNCTION public.insert_city(name_city1   varchar
                                            , country1     varchar
                                            , province1    varchar
                                            , region1      varchar
                                            , cap1         varchar
                                            , nationality1 varchar)
  RETURNS integer AS
$func$
   WITH ins AS (
      INSERT INTO city
            (name_city , country , province , region , cap , nationality ) 
      VALUES(name_city1, country1, province1, region1, cap1, nationality1)
      ON     CONFLICT (name_city) DO UPDATE
      SET    name_city = NULL WHERE FALSE  -- never executed, but locks the row!
      RETURNING id_city
      )
   SELECT id_city FROM ins
   UNION  ALL
   SELECT id_city FROM city WHERE name_city = name_city1  -- only executed if no INSERT
   LIMIT  1;
$func$  LANGUAGE sql;

要点

• 假设您运行 Postgres 9.5 或更高版本，因为您没有声明它。

• 使用新的更快的 UPSERT 解决方案 INSERT .. ON CONFLICT ...
  详细解释:

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

• 为此，您需要 name_city 上的 UNIQUE 约束。

• 关于 UNION ALL ... LIMIT 1:

  • Way to try multiple SELECTs till a result is available?

• 可以使用数据修改 CTE 通过单个 SQL 命令实现。这最不容易受到锁争用或其他并发问题的影响。即使没有并发访问，它也是最短和最快的。

• 该函数可以是一个更简单的SQL 函数。 (但 plpgsql 也没有错或坏。)

• 不要滥用 ALIAS FOR 将名称附加到参数。手册中明确不鼓励这样做。使用正确的参数名称。 The manual:

  It's best to use it only for the purpose of overriding predetermined names.