Re: SET within a function? - Mailing list pgsql-general

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 darren@crystalballinc.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=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=26ef31219ae11442&seekm=3DF52206.5060507%40mascari.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
> > > mascarm@mascari.com
> > >
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
> --
> Darren Ferguson
>
>