473,718 Members | 1,964 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 #1
24 1725
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSenti re.com> wrote:

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?


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

Nov 12 '05 #2
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:
On Mon, Oct 13, 2003 at 21:16:33 -0400,
Edmund Dengler <ed*****@eSenti re.com> wrote:

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?


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


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

Nov 12 '05 #3
Edmund Dengler wrote:
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.


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

Nov 12 '05 #4
Edmund Dengler wrote:
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.


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

Nov 12 '05 #5
> 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.


I ended up writing an "equivalent " function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.

---------------------------(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 #6
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.

Regards,
Ed

On Tue, 14 Oct 2003, Arthur Ward wrote:
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.


I ended up writing an "equivalent " function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 12 '05 #7
Edmund Dengler wrote:
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.


IIRC, NULL values aren't indexed, only actual values, which is an
implementation detail but yet-another reason why NULL-elimination
through normalization is a good idea:

http://www.hughdarwen.freeola.com/Th...hout-nulls.pdf
Mike Mascari
ma*****@mascari .com

---------------------------(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 #8
> 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.


I ended up writing an "equivalent " function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.

---------------------------(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 #9
The problem I would face is that this still needs to be a sequential scan
in the table rather than an index lookup.

Regards,
Ed

On Tue, 14 Oct 2003, Arthur Ward wrote:
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.


I ended up writing an "equivalent " function for the project I'm working
on. It goes like this in plpgsql:

IF $1 IS NULL THEN
RETURN $2 IS NULL;
ELSIF $2 IS NULL THEN
-- We already know $1 is not null.
RETURN FALSE;
ELSE
-- Both args are not null.
RETURN $1 = $2;
END IF;

That's the basic idea. I put a wrapper around this to generate a copy of
it for all the data types used in my database.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 12 '05 #10

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

Similar topics

4
2317
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
2101
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
3489
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
4478
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
2107
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
8566
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
2190
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
6057
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
2639
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
2380
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
8827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9354
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
9120
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
9053
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
5971
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
4481
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
4741
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2553
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2122
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.