Get id from INSERT or SELECT

问题:

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 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;

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.

How can I do this?

回答1:

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;

Major points

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

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

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

• You need a UNIQUE constraint on name_city for this.

• About UNION ALL ... LIMIT 1:

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

• 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.

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

• 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.

回答2:

Why not alter your function like so?:

Insert the existing id_city into id_city1. If one does not exist, it will be NULL. You can then perform the INSERT if it is NULL and assign the new id_city1. Finally return 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;

回答3:

This is plpgsql version

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;

There is a way that when the row exist don't increase the primary_key number (id_city in this case) ?