我正在try 编写一个PostgreSQL函数,该函数根据指定的条件复制表的所有行,并返回现有行ID和复制的行ID的映射.
以下是我要使用的示例数据.
CREATE TABLE foo (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 6),
foo_content text NOT NULL,
condition_col integer NOT NULL
);
INSERT INTO foo OVERRIDING SYSTEM VALUE VALUES
( 1 , 'Sing, O goddess', 99),
( 2 , 'the anger of Achilles', 99),
( 3 , 'son of Peleus', 99),
( 4 , 'that brought countless ills', 2),
( 5 , 'upon the Achaeans', 3);
SELECT * FROM foo;
这是我try 编写能够复制条目的SQL函数:
CREATE OR REPLACE FUNCTION duplicate_entries(condition INT, new_condition INT)
RETURNS TABLE (
old_id INT,
new_id INT) AS
$$
BEGIN
RETURN QUERY (
WITH select_cte AS (
SELECT id
FROM foo f
WHERE f.condition_col = condition
), insertion_cte AS (
INSERT INTO foo (foo_content, condition_col)
SELECT f.foo_content, new_condition
FROM select_cte s
JOIN foo f ON s.id = f.id
RETURNING id
)
--- how to I return a mapping of ids from here?
);
END;
$$ LANGUAGE plpgsql;
-- To call the function and get the result
SELECT * FROM duplicate_entries(99, 100);
-- To see the updated 'foo' table
SELECT * FROM foo;
在这里,虽然我已经实现了期望的函数,但我无法从该函数返回old_id和new_id的映射.
返回的表应该是这样的.我曾try 使用Union all查询,但似乎没有起到作用.我认为临时表在这种情况下可能会有所帮助,但我不想使用临时表,因为这可能是一种糟糕的做法.
下面是我想要的输出:
old_ids | new_ids |
---|---|
1 | 6 |
2 | 7 |
3 | 8 |