473,836 Members | 2,068 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 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 #11
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #12
You can try COALESCE function where by if you have a null you can make it
have a default value and that default value will be a value the field
never reaches

i.e. COALESCE(NULL,' ') = COALESCE(NULL,' ')

HTH
Darren

On Tue, 14 Oct 2003, Edmund Dengler wrote:
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org


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

Nov 12 '05 #13
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?

Regards,
Ed

On Tue, 14 Oct 2003 da****@crystalb allinc.com wrote:
You can try COALESCE function where by if you have a null you can make it
have a default value and that default value will be a value the field
never reaches

i.e. COALESCE(NULL,' ') = COALESCE(NULL,' ')

HTH
Darren

On Tue, 14 Oct 2003, Edmund Dengler wrote:
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson


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

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

Nov 12 '05 #14
Edmund Dengler wrote:
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?


Yes, but I think you have to write a little wrapper:

CREATE TABLE foo (
key integer not null,
value text);

CREATE FUNCTION toValue(text) RETURNS text AS '

SELECT COALESCE($1, '''');

' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX i_foo1 ON foo(toValue(val ue));

And always be sure to use the function in the query:

SELECT *
FROM foo
WHERE toValue(value) = '';

For fun:

SET enable_seqscan to off;

EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';

should produce an Index Scan....

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 #15
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 #16
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #17
You can try COALESCE function where by if you have a null you can make it
have a default value and that default value will be a value the field
never reaches

i.e. COALESCE(NULL,' ') = COALESCE(NULL,' ')

HTH
Darren

On Tue, 14 Oct 2003, Edmund Dengler wrote:
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org


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

Nov 12 '05 #18
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?

Regards,
Ed

On Tue, 14 Oct 2003 da****@crystalb allinc.com wrote:
You can try COALESCE function where by if you have a null you can make it
have a default value and that default value will be a value the field
never reaches

i.e. COALESCE(NULL,' ') = COALESCE(NULL,' ')

HTH
Darren

On Tue, 14 Oct 2003, Edmund Dengler wrote:
An issue is that I am trying to avoid having another table (to stop the
need for a join (performance reasons)). The NULLs are relatively rare, but
since they can appear, and in certain pathological cases the sequential
scan can take seconds to run, I was hoping for a work-around. But it looks
like I have no real choice in this as there is no way to specify that
NULL == NULL.

Another question: if I have a multi-column index, and one of the values of
a tuple is NULL, is that row not indexed? If it is, how does this jibe
with the "NULLs are not indexed" statements?

Thanks!
Ed

On Tue, 14 Oct 2003, Mike Mascari wrote:
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 6: Have you searched our list archives?

http://archives.postgresql.org


--
Darren Ferguson


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

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

Nov 12 '05 #19
Edmund Dengler wrote:
This just returns us to the problem that the use of a function causes a
sequential scan (as the select has to essentially apply the function to
each row). I would need to store a dummy value into the field (it is an
int, so I could store -1, but it breaks my sense of aesthetics to do this
simply to get around the sequential scan).

Could I use a functional index, maybe?


Yes, but I think you have to write a little wrapper:

CREATE TABLE foo (
key integer not null,
value text);

CREATE FUNCTION toValue(text) RETURNS text AS '

SELECT COALESCE($1, '''');

' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX i_foo1 ON foo(toValue(val ue));

And always be sure to use the function in the query:

SELECT *
FROM foo
WHERE toValue(value) = '';

For fun:

SET enable_seqscan to off;

EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';

should produce an Index Scan....

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 #20

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
9820
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
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
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
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();...
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
2
4016
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.