Получить идентификатор от ВСТАВИТЬ или ВЫБРАТЬ

Question

У меня есть эта функция, которая вставляет строку в city стол без дубликатов. Возвращает идентификатор вставленной строки:

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 ...
  Детальное объяснение:

  • SELECT или INSERT в функции склонны к условиям гонки?

• Тебе необходимо UNIQUE ограничение на name_city за это.

• Около UNION ALL ... LIMIT 1:

  • Способ попробовать несколько SELECT, пока не будет доступен результат?

• Может быть достигнуто с помощью одной команды SQL с использованием CTE, модифицирующей данные. Это наименее уязвимо для конкуренции за блокировку или других проблем параллелизма. Он самый короткий и быстрый даже без одновременного доступа.

• Функция может быть более простой функцией SQL. (Но plpgsql тоже не плохой или плохой.)

• Не ругайте ALIAS FOR прикрепить имена к параметрам. Это явно не рекомендуется в руководстве. Используйте правильные имена параметров. Руководство:

  Лучше всего использовать его только с целью переопределения заранее определенных имен.

Answer 2

Почему бы не изменить свою функцию так:

Вставьте существующий id_city в id_city1, Если он не существует, он будет NULL, Затем вы можете выполнить INSERT если это NULL и назначить новый id_city1, Наконец вернусь id_city1,

SELECT id_city INTO id_city1 FROM city WHERE name_city = name_city1;

IF id_city1 IS NULL THEN

    INSERT INTO city (name_city, country, province, region, cap, nationality) 
    VALUES (name_city1, country1, province1, region1, cap1, nationality1)
    RETURNING id_city INTO id_city1;

END IF;

RETURN id_city1;

Answer 3

Это версия plpgsql

CREATE OR REPLACE FUNCTION public.insert_city(name_city1   varchar
                                        , country1     varchar
                                        , province1    varchar
                                        , region1      varchar
                                        , zip1         varchar
                                        , nationality1 varchar,
                                        OUT id_city1 int)
  AS
 $func$
 BEGIN
    INSERT INTO city
        (name_city , country , province , region , zip , nationality ) 
    VALUES(name_city1, country1, province1, region1, zip1, nationality1)
    ON CONFLICT (name_city,zip) DO UPDATE
    SET    name_city = NULL WHERE FALSE  -- never executed, but locks the row!
    RETURNING id_city
    INTO id_city1;

    IF NOT FOUND THEN
        SELECT id_city
        FROM city
        WHERE name_city = name_city1
        INTO id_city1;
    END IF;
  END  $func$  LANGUAGE plpgsql;

Есть способ, которым, когда строка существует, не увеличивайте число primary_key (id_city в этом случае)?