473,836 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SET within a function?

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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05
24 1737
Edmund Dengler <ed*****@eSenti re.com> writes:
... I have no real choice in this as there is no way to specify that
NULL == NULL.


The conventional wisdom on this is that if you think you need NULL ==
NULL to yield true, then you are misusing NULL, and you'd better
reconsider your data representation. The standard semantics for NULL
really do not support any other interpretation of NULL than "I don't
know what this value is". If you are trying to use NULL to mean
something else, you will face nothing but misery. Choose another
representation for whatever you do mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #21
I guess it comes back to the semantics of NULL. As has been pointed out in
many a database course, what we mean by NULL changes, and how we want to
use NULL changes on circumstances.

Normally, when I am comparing rows, I do want NULL <> NULL. In
this specific instance, no value has been assigned to the specific
column for this row, so NULL is appropriate. However, there are cases
where I am trying to explicitely test for existence of a specific row
in the table, and in this case, I _do_ want a NULL == NULL type of
comparison. I could try and specify a dummy value (in this case, I could
put in -1), but then I am trying to create a second class of NULLs, and
this is usually not considered good design.

Note that as a prime example of how postgresql itself is not "consistent "
(in the strictest sense) is GROUP BY which treats NULL == NULL
(interesting side bar, is there a way to cause GROUP BY to treat NULLs as
not equal to each other?). In a theoretical question, how is this
justified if NULL should not equal to NULL (other than "it is in the
spec")?

Also, is there a particular reason for not having a strict equality
operator (or is it simply because it is not in the specification)?
Performance? No support from the back-end? Something else?

Regards,
Ed

On Wed, 15 Oct 2003, Tom Lane wrote:
Edmund Dengler <ed*****@eSenti re.com> writes:
... I have no real choice in this as there is no way to specify that
NULL == NULL.


The conventional wisdom on this is that if you think you need NULL ==
NULL to yield true, then you are misusing NULL, and you'd better
reconsider your data representation. The standard semantics for NULL
really do not support any other interpretation of NULL than "I don't
know what this value is". If you are trying to use NULL to mean
something else, you will face nothing but misery. Choose another
representation for whatever you do mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #22
Edmund Dengler <ed*****@eSenti re.com> writes:
Normally, when I am comparing rows, I do want NULL <> NULL.
No, you still haven't got the point. NULL is not equal to NULL, and
it is not not-equal-to NULL either. The result of the comparison is
NULL, not true or false. This is consistent with the interpretation
of NULL as "I don't know the value". If you don't know what the value
is, you also don't know whether it is equal to some other value.
Note that as a prime example of how postgresql itself is not "consistent "
(in the strictest sense) is GROUP BY which treats NULL == NULL
Shrug ... the standard tells us to do that. SQL has never been held up
as a model of consistency.
Also, is there a particular reason for not having a strict equality
operator (or is it simply because it is not in the specification)?


The existing operators *are* strict (which is defined as NULL in yields
NULL out). You could build a set of non-strict comparison operators if
you had a mind to. IIRC you would lose some potential hashtable
optimizations, but in the main it would work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #23
Tom Lane wrote:
Edmund Dengler <ed*****@eSenti re.com> writes:

Normally, when I am comparing rows, I do want NULL <> NULL.


No, you still haven't got the point. NULL is not equal to NULL, and
it is not not-equal-to NULL either. The result of the comparison is
NULL, not true or false. This is consistent with the interpretation
of NULL as "I don't know the value". If you don't know what the value
is, you also don't know whether it is equal to some other value.


In these cases, it is recommended to either find a value which is out of
range, normally, and use that in place of NULL. For examples:

-1
10^32-1
"."
the_oldest_poss ible_date BC
the_furthest_aw ay_date AD

Another way is to put an additional column in, but I think this still
has problems if you are trying to get a query to return values in a
column that has NULLs and you are querying against the column that has
the NULLs.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #24
On Wed, 15 Oct 2003, Edmund Dengler wrote:
Note that as a prime example of how postgresql itself is not "consistent "
(in the strictest sense) is GROUP BY which treats NULL == NULL
(interesting side bar, is there a way to cause GROUP BY to treat NULLs as
not equal to each other?). In a theoretical question, how is this
justified if NULL should not equal to NULL (other than "it is in the
spec")?


Because it's not defined in terms of equality. ;) GROUP BY is defined by
value "distinct"n ess, where distinct has a very specific definition in the
spec (which treats two NULL values as not distinct).

You might actually be able to find some way to use that to your advantage,
but I'm not sure how.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #25

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2324
by: Deane Barker | last post by:
I have a function that selects a file to include, then includes is. The file is including within the function, like so: function include_file($file_name) { require $file_name; return; }
2
2108
by: Chris Haynes | last post by:
Hello all, I have a structure: typedef struct UVstruct { float u, v; } uv; Inside a function (A) i declare a pointer to an instance of this structure:
4
3496
by: Ralph Noble | last post by:
Does anyone know of a string function in Access that will allow me to count the number of instances one string occurs within another? Or if there is some sort of word count function? If there is, I'm not aware of it. I've tried the following unsuccessfully ... SELECT *, (Len()-Len(Replace(,' what_im_looking_to_count','')))/(Len(' what_im_looking_to_count')) AS KeywordFoundCountFROM tblYourTable; That'll find how many times the phrase...
8
4480
by: jody.florian | last post by:
Hi, I'm trying to use preg_replace_callback within a method. The preg_replace_callback() & mycallback() pair will only be used by this method, and this method will probably only be called once in the object's lifetime (so there's no sense in making the callback function a method of the class...(is there??)) Instead, I wanted to use create_function() to create a make-shift callback function within the method. (seeing as you can't...
9
2113
by: 47computers | last post by:
Pretty new to PHP, I recently started learning about error trapping. As of right now, I include the following into a page in my website: -------BEGIN PASTE-------- error_reporting(E_ERROR | E_PARSE); set_error_handler("SendErrorReport"); function SendErrorReport($errorNumber, $errorMessage, $errorFile, $errorLine, $vars) {
1
8572
by: Elmo Watson | last post by:
I've inherited a project that has quite a few Table rows - inside each cell, within the row are a couple of textboxes ("in" and "out"), along with a "total" label. Also - this is all within a Content Page/Master Page scenario - I've got the function inside the Content page, along with the all the items herein On each row, each of the in and out and total controls have a number associated with them, like:
0
2194
by: robgallen | last post by:
I have 2 user controls within a master page, and I would like one of them to call a function in the other. All the examples I have seen involve a page communicating with the Master page, or with a user control communicating with the Master page, not with a user control directly talking to another user control, bypassing the page itself. So, I have the following files: Haep.master - which contains:
4
6073
by: Harlequin | last post by:
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase this is all very difficult to explain in words, please bear with me as I endevour to explain what it is I am trying to do. It would be helpful if I could attach a graphics file to this posting that would help explain what it is I'm trying to achieve...
5
2646
by: hurricane_number_one | last post by:
I'm trying to have a class, which uses threads be able to raise events to the form that created it. I've seen solutions around the net for this, but I didn't really like their implementation. Most involve passing the form into the class, or require a lot of coding. All I really need to do is be able to have my thread call a function within that class which runs on the same thread as the class. I've done this from within forms, but...
7
2385
by: vunet | last post by:
I am still not clear about how to reference an object within another object to pass first object to a function: var Parent = { myFunc : function(){ alert("Parent = "+this) }, Child : { //how to get reference to Parent? myChildFunc : function(){
0
9670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10844
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5649
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4452
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3115
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.