获取由函数复制的数据

编程入门 行业动态 更新时间:2024-10-10 05:18:58
本文介绍了获取由函数复制的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

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

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.

更多推荐

获取由函数复制的数据

本文发布于:2023-11-22 00:28:55,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1615273.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:函数   数据

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!