Re: SELECT based on function result

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