Returning ID from the upsert procedure in language sql postgresql-CodePudding

I have a SQL upsert query and I want to return inserted record ID or updated record ID of the locations table. What changes do I need in the query? (Note: the name column is unique)

CREATE OR REPLACE PROCEDURE locations_upsert
(
    _name        TEXT,
    _address     TEXT,
    _postal_code TEXT,
    _country     TEXT
)
LANGUAGE SQL
AS $$
    INSERT INTO locations
    (
        name,
        address,
        postal_code,
        country
    )
    VALUES
    (
        _name,
        _address,
        _postal_code,
        _country
    )
    ON CONFLICT (name)
    DO UPDATE  
    SET 
        address = _address,
        postal_code = _postal_code,
        country = _country
$$;

CodePudding user response：

Change definition of procedure to function which returns integer value and use

INSERT INTO ... RETURNING id

UPDATE ... RETURNING id

expression.

CodePudding user response：

Change the procedure to a function and declare either int or bigint as return type:

CREATE OR REPLACE FUNCTION locations_upsert(
  _name        TEXT,
  _address     TEXT,
  _postal_code TEXT,
  _country     TEXT
) RETURNS bigint
AS $$ 
  INSERT INTO locations  
    (name, address, postal_code, country) VALUES
    (_name,_address,_postal_code,_country)
   ON CONFLICT (name)
   DO UPDATE SET 
     address = _address,
     postal_code = _postal_code,
     country = _country
   RETURNING id;    
$$ LANGUAGE sql;

Alternatively you can use the table name as return data type - RETURNS locations -, which will enable you to return the whole record - RETURNING *:

CREATE OR REPLACE FUNCTION locations_upsert(
  _name        TEXT,
  _address     TEXT,
  _postal_code TEXT,
  _country     TEXT
) RETURNS locations
AS $$ 
  INSERT INTO locations  
    (name, address, postal_code, country) VALUES
    (_name,_address,_postal_code,_country)
    ON CONFLICT (name)
    DO UPDATE SET 
      address = _address,
      postal_code = _postal_code,
      country = _country
  RETURNING *;  
$$ LANGUAGE sql;

Demo: db<>fiddle