获取由函数复制的数据 [英] Get data copied by a function

问题描述

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

I have a quite complicated data structure that lies in several tables. I have a function that makes a copy of that structure. I want to make a copy and get newly created data in a single query like this:

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

已成功创建副本，但上述查询未返回该副本。我猜对于外部查询还是 committed 来说仍然不可见。

The copy is successfully created, but is not returned by the above query. I guess it is not yet visible for the outer query or somehow committed.

我也尝试使用 ...选择... 但没有成功...

I have also tried to use WITH ... SELECT ... but with no success...

函数 make_copy（id）被声明为 VOLATILE ，因为它修改了数据库，并且具有相同参数的多次调用将创建多个副本。

The function make_copy(id) is declared as VOLATILE because it modifies the database, and multiple calls with the same parameter will create multiple copies.

可能的解决方案可能是 make_copy（id）函数将返回整个新数据结构（ SELECT * FROM make_copy（old_id）），但将需要很多别名（许多表具有 id 或 name 列）。同样，我最终会在很多地方建立（读取）该数据结构。

Possible solution could be that make_copy(id) function would return the whole new data structure (SELECT * FROM make_copy(old_id)) but it would require many aliasing (many tables have id or name column). Also I would end up with many places to build (read) that data structure.

我如何才能调用该函数并将其结果（以及所有副作用）合而为一

How can I call that function and use its result (and all side effects) in one query?

推荐答案

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

I'm afraid that's not possible without splitting it into two queries.

CTE无法帮助您- WITH 中的数据修改语句（请参见在cte中带有更新表的示例）：

CTE can't help you - Data-Modifying Statements in WITH (See there example with updating table inside of the cte):

... WITH中的子语句与其他
和主查询同时执行。因此，在WITH中使用数据修改
语句时，指定的更新
实际发生的顺序是不可预测的。所有语句都使用相同的
快照执行（请参见第13章），因此它们无法看到彼此在目标表上的作用
。这减轻了
实际行更新顺序的不可预测性的影响，并且意味着
RETURNING数据是在
不同的WITH子语句与主查询之间传递更改的唯一方法。 ..

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

我想您也不能使用函数- 函数波动类别 ：

And I guess you cannot do this with function either - Function Volatility Categories:

对于用SQL或任何标准过程性
语言编写的函数，还有第二个重要属性，由
波动率类别确定，即任何调用函数的SQL命令已对
进行了数据更改。
VOLATILE函数将看到此类更改，而STABLE或IMMUTABLE
函数则不会。 ... VOLATILE函数在执行每个查询时在
处获取新的快照。

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.

这篇关于获取由函数复制的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！