Re: SELECT based on function result

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