postgresql - 获取函数复制的数据

Question

我有一个非常复杂的数据结构，它位于多个表中。我有一个函数可以复制该结构。我想在这样的单个查询中制作副本并获取新创建的数据:

SELECT
    *
FROM
    main_table
    JOIN other_table
    ON (main_table.id = other_table.main_id)
WHERE
    main_table.id = make_copy(old_id);

副本已成功创建，但上述查询未返回。我猜它对于外部查询还是不可见的，或者以某种方式提交。

我也尝试过使用 WITH ... SELECT ... 但没有成功...

函数 make_copy(id) 被声明为 VOLATILE 因为它修改了数据库，使用相同参数的多次调用将创建多个副本。

可能的解决方案是 make_copy(id) 函数将返回全新的数据结构 (SELECT * FROM make_copy(old_id)) 但它需要许多别名 (许多表都有 id 或 name 列)。此外，我最终会在很多地方构建(读取)该数据结构。

我如何调用该函数并在一个查询中使用它的结果(以及所有副作用)？

Answer 1

如果不将其拆分为两个查询，恐怕这是不可能的。

CTE 帮不了你 - Data-Modifying Statements in WITH (请参阅在 cte 中更新表的示例):

...The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query...

而且我猜你也不能用函数来做到这一点 - Function Volatility Categories :

For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. ... VOLATILE functions obtain a fresh snapshot at the start of each query they execute.