我有一個 SQL upsert 查詢，我想回傳位置表的插入記錄 ID 或更新記錄 ID。我需要在查詢中進行哪些更改？（注：該name列是唯一的）
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
$$;
uj5u.com熱心網友回復：
將程序的定義更改為回傳整數值并使用的函式
INSERT INTO ... RETURNING id
UPDATE ... RETURNING id
表達。
uj5u.com熱心網友回復：
程序不能回傳值。將程序更改為函式并將其宣告為int或bigint回傳型別：
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;
或者，您可以使用表名作為回傳資料型別 - RETURNS locations-，這將使您能夠回傳整個記錄 - 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;
演示： db<>fiddle
進一步閱讀： PostgreSQL CREATE PROCEDURE
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/349171.html
標籤：sql PostgreSQL 存储过程 sql函数
上一篇：用SQL中的變數替換子字串