Thread: SELECT based on function result

I have a function that tells me if a record is positive and negative
based on several field values. I use it in select statements:

ohc=> SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS
positive FROM tblleadwipe WHERE hud_building_id IS NOT NULL;
 sample_id | positive
-----------+----------
        73 | f
        81 | t
(2 rows)

I see that I cannot change my WHERE statement to WHERE positive = 't'
because the column positive does not exist. Now I am looking for the
best way to return all records that are found positive or negative using
a query. Can anyone offer any guidance to how I can return all the
positives (or negatvies)? Or do I need to write another function that
does that?

--
Robert

Did you try creating a view with that select statement, then just
selecting from the view? I don't know if there is a restriction on using
a function like that in a view definition or not.

-DAP

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Robert
Fitzpatrick
Sent: Thursday, July 15, 2004 4:31 PM
To: PostgreSQL
Subject: [GENERAL] SELECT based on function result

I have a function that tells me if a record is positive and negative
based on several field values. I use it in select statements:

ohc=> SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS
positive FROM tblleadwipe WHERE hud_building_id IS NOT NULL;  sample_id
| positive
-----------+----------
        73 | f
        81 | t
(2 rows)

I see that I cannot change my WHERE statement to WHERE positive = 't'
because the column positive does not exist. Now I am looking for the
best way to return all records that are found positive or negative using
a query. Can anyone offer any guidance to how I can return all the
positives (or negatvies)? Or do I need to write another function that
does that?

--
Robert


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match

Robert Fitzpatrick <robert@webtent.com> writes:
> SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS
> positive FROM tblleadwipe WHERE hud_building_id IS NOT NULL;

> I see that I cannot change my WHERE statement to WHERE positive = 't'
> because the column positive does not exist.

What have you got against

  SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS positive
  FROM tblleadwipe WHERE is_wipe_positive(tblleadwipe.sample_id) = 't';

If you're concerned about the notational overhead of writing the
expression twice, you could use a sub-select:

  SELECT sample_id, positive FROM
   (SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS positive
    FROM tblleadwipe) AS sub
  WHERE positive = 't';

Bear in mind though that the planner is quite likely to flatten this
into the same case as above; so this is only a notational device and
not a way to save any execution time.

If you are trying to avoid two evaluations of is_wipe_positive() per
row, there isn't any real clean way to do that, because standard SQL
doesn't have any concept of avoiding duplicate evaluations.  There
are various ways you can fake out the Postgres planner --- at the moment
I'd suggest an OFFSET 0, viz

  SELECT sample_id, positive FROM
   (SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS positive
    FROM tblleadwipe OFFSET 0) sub
  WHERE positive = 't';

to prevent the sub-select from being flattened into the outer query.
But realize that what you are doing here is disabling macro-optimization
in favor of your own ideas about micro-optimization.  For example, if
you had an index on is_wipe_positive(tblleadwipe.sample_id) then the
second form would very possibly perform far worse than the first,
because it could not use the index.

            regards, tom lane

Robert Fitzpatrick wrote:

> I have a function that tells me if a record is positive and negative
> based on several field values. I use it in select statements:
>
> ohc=> SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS
> positive FROM tblleadwipe WHERE hud_building_id IS NOT NULL;
>  sample_id | positive
> -----------+----------
>         73 | f
>         81 | t
> (2 rows)
>
> I see that I cannot change my WHERE statement to WHERE positive = 't'
> because the column positive does not exist. Now I am looking for the
> best way to return all records that are found positive or negative using
> a query. Can anyone offer any guidance to how I can return all the
> positives (or negatvies)? Or do I need to write another function that
> does that?
>
> --
> Robert
>

Any reason why:

SELECT sample_id, positive
  FROM (SELECT sample_id,
               is_wipe_positive(tblleadwipe.sample_id) AS positive
          FROM tblleadwipe
          WHERE hud_building_id IS NOT NULL) foo
  WHERE positive = 't';

won't work?

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match