在函数内设置？ [英] SET within a function?
问题描述
大家好！
我正在做一些需要在另一个
表中找到元组的触发函数。问题是这第二个表正在做一些总结
工作，我需要空值相互等于。
基本上，在触发器中我做了一个：
SELECT INTO ... x
FROM table1
WHERE ...（有些直截了当x = old.x） ...
AND（x1 = old.x1 OR（x1为null，old.x1为空））
AND（x2 = old.x2 OR（x2是null和old.x2为null））
AND（x3 = old.x3 OR（x3为null，old.x3为null））;
问题是索引用于执行简单的东西，然后x1，x2，x3通过索引扫描完成，而不是直接扫描。
不幸的是对于我拥有的数据集，它可以很简单地聚集在一起，并且每次调用扫描可以花费多个
秒。
我想如果我能做一个''SET TRANSFORM_NULL_EQUALS TO ON'那么这可能
修复这个问题（不知道，没有'还没试过呢。我的问题是：这个
可以在一个函数内完成，这样在函数结束时，值
会在输入时重置为值（有点像' 'SET LOCAL''除了
只是函数调用的长度）。这可能吗？
谢谢！
Ed
----------- ----------------（广播结束）---------------------------
提示2：您可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
Hi all!
I am doing some trigger functions that need to find a tuple in another
table. The problem is that this second table is doing some summarization
work, and I need nulls to equal each other.
Basically, in the trigger I do a:
SELECT INTO ... x
FROM table1
WHERE ...(some straightforward x = old.x)...
AND (x1 = old.x1 OR (x1 is null and old.x1 is null))
AND (x2 = old.x2 OR (x2 is null and old.x2 is null))
AND (x3 = old.x3 OR (x3 is null and old.x3 is null));
The problem is that an index is used to perform the straightforward stuff,
and then the x1,x2,x3 is done via an index scan, rather than directly.
Unfortunately for the data set I have, it can be clustered pretty badly
around the straightforward stuff, and so the scan can take multiple
seconds per call.
I think if I could do a ''SET TRANSFORM_NULL_EQUALS TO ON'' then this might
fix the issue (don''t know, haven''t tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like ''SET LOCAL'' except for
just the length of the function call). Is this possible?
Thanks!
Ed
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
推荐答案
在星期一，2003年10月13日21:16:33 -0400，
Edmund Dengler< ed ***** @ eSentire.com>写道：
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSentire.com> wrote:
我想如果我能做'SET TRANSFORM_NULL_EQUALS TO ON'那么这可能会解决问题（不知道，不是试了一下）。我的问题是：这可以在一个函数内完成，这样在函数结束时，值
会在输入时重置为值（类似于''SET LOCAL''除了
只是函数调用的长度）。这可能吗？
I think if I could do a ''SET TRANSFORM_NULL_EQUALS TO ON'' then this might
fix the issue (don''t know, haven''t tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like ''SET LOCAL'' except for
just the length of the function call). Is this possible?
我不认为这会做你想要的。该设置用于
rewrite = null as null，不改变任何东西以使null匹配每个
其他。
---------------------------（广播结束）----------------- ----------
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
I don''t think that will do what you want. That setting is used to
rewrite = null as is null, not to change things so that nulls match each
other.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
仅用于重写文字''X = NULL''或它会对'X = OLD.X'这样的值进行测试
（并且重写是OLD.X是NULL）？
有没有办法让NULLS相互匹配（因为我正在寻找一个
文字行，而不是使用NULL作为UNKNOWN）。我想我可以为''Not a valid value''输入一个
的虚拟值，但是当我真的想要NULL时，它看起来很尴尬
。
问候！
Ed
2003年10月13日星期一，Bruno Wolff III写道：
Is the rewrite only for the literal ''X = NULL'' or will it do a test
against a value such as ''X = OLD.X'' (and rewrite is OLD.X is NULL)?
Is there any way to match NULLS to each other (as I am looking for a
literal row, not using NULL as the UNKNOWN). I suppose I could put in a
dummy value for the ''Not a valid value'', but it seems to be quite awkward
when I really do want the NULL.
Regards!
Ed
On Mon, 13 Oct 2003, Bruno Wolff III wrote:
2003年10月13日星期一21:16:33 -0400，
Edmund Dengler< ed ***** @ eSentire.com>写道：
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSentire.com> wrote:
我想如果我能做'SET TRANSFORM_NULL_EQUALS TO ON'那么这可能会解决问题（不知道，不是试了一下）。我的问题是：这可以在一个函数内完成，这样在函数结束时，值
会在输入时重置为值（类似于''SET LOCAL''除了
只是函数调用的长度）。这可能吗？
I think if I could do a ''SET TRANSFORM_NULL_EQUALS TO ON'' then this might
fix the issue (don''t know, haven''t tried it yet). My question is: can this
be done within a function such that at the end of the function, the value
is reset back to value upon entering (kind of like ''SET LOCAL'' except for
just the length of the function call). Is this possible?
我不认为这会做你想要的。该设置用于
rewrite = null为null，不改变任何东西，以便空值匹配每个
----------- ----------------（广播结束）---------------------------
http：// archives.postgresql.org
------------------------- - （广播结束）---------------------------
提示7：别忘了增加你的可用空间地图设置
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
Edmund Dengler写道：
Edmund Dengler wrote:
是否仅为文字重写' 'X = NULL''或者它会针对诸如''X = OLD.X''之类的值进行测试（并且重写是OLD.X是NULL）？
这是一个解析时间转换：
http：// groups .google.com / groups？hl = e ... cari.com＃link6
有没有办法让NULLS相互匹配（因为我正在寻找一个
文字行，不使用NULL作为UNKNOWN）。我想我可以为''Not a valid value''输入一个虚拟值，但是当我确实想要NULL时，它似乎很尴尬。
Is the rewrite only for the literal ''X = NULL'' or will it do a test
against a value such as ''X = OLD.X'' (and rewrite is OLD.X is NULL)?
It is a parse time transformation:
http://groups.google.com/groups?hl=e...cari.com#link6
Is there any way to match NULLS to each other (as I am looking for a
literal row, not using NULL as the UNKNOWN). I suppose I could put in a
dummy value for the ''Not a valid value'', but it seems to be quite awkward
when I really do want the NULL.
规范化可以通过另一个
关系来消除NULL，其候选键与原始表相同，但
这些记录其属性为NULL将不会出现在孩子表的
中。
另一种可能的解决方案是用内部定义自己的类型
$'
状态''不是有效值''...
HTH，
Mike Mascari
< a href =mailto：ma ***** @ mascari.com> ma ***** @ mascari.com
------ ---------------------（广播结束）------------------------ ---
提示2：您可以使用取消注册命令一次性取消所有列表
（将注册YourEmailAddressHere发送到 MA ******* @ postgresq l.org ）
Normalization would have you eliminate the NULL by having another
relation whose candidate key is the same as your original table, but
those records whose attribute is NULL would simply not be present in
the child table.
Another possible solution is to define your own type with an internal
status for ''Not a valid value''...
HTH,
Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
这篇关于在函数内设置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
