从INSERT或SELECT获取ID [英] Get id from INSERT or SELECT

问题描述

我有这个功能，可以将行插入到city表中，而不能重复.它返回插入行的ID:

I've this function that inserts a row into a city table without duplicates. It returns the id of the inserted row:

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标记了我需要这样的地方:

xxx marks the spot where I need something like this:

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.

If the first query does not insert a new row and I don't get an id_city from it, I want to execute the second query to select an existing id_city.

我该怎么做?

推荐答案

您的函数可以进一步简化.更重要的是，您可以修复内置的竞争条件:

Your function can be simplified some more. More importantly, you can fix the built-in race condition:

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 或更高版本，因为您没有声明它.

  Major points

  • Assuming you run Postgres 9.5 or later, since you did not declare it.

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

    Use the new faster UPSERT solution INSERT .. ON CONFLICT ...
    Detailed explanation:

    • SELECT或INSERT函数是否容易出现竞争状况?

    为此，您需要在name_city上使用 UNIQUE 约束.

    You need a UNIQUE constraint on name_city for this.

    关于UNION ALL ... LIMIT 1:

    • 尝试多个SELECT的方法直到结果可用?

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

    Can be achieved with a single SQL command using a data-modifying CTE. This is least vulnerable to lock contention or other concurrency issues. It's shortest and fastest even without concurrent access.

    该函数可以是更简单的 SQL函数. (但是plpgsql也不是错误或不好的.)

    The function can be a simpler SQL function. (But plpgsql isn't wrong or bad either.)

    请勿滥用 ALIAS FOR 将名称附加到参数.手册中明确建议不要这样做.使用正确的参数名称. 手册:

    Don't abuse ALIAS FOR to attach names to parameters. That's explicitly discouraged in the manual. Use proper parameter names. The manual:

    最好仅将其用于覆盖预定名称的目的.

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

  这篇关于从INSERT或SELECT获取ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！