How can I write a SELECT statement in which the condition is the result of a function

Question

I want to write a SELECT statement as follows:

SELECT field_a 
FROM my_table
WHERE field_b IN (my_function(field_c)).

Is that possible?

my_function would have to return an array?

I'm using PostgreSQL 8.2

Answer 1

SELECT  *
FROM    (
        SELECT  field_a, field_b, my_function(field_c) fc
        FROM    mytable
        ) q
WHERE   field_b = fc

The return type of your function should be SETOF (e. g. SETOF INT, SETOF VARCHAR etc.)

Note that you can use an IN style expression like this:

SELECT  field_a, field_b
FROM    mytable
WHERE   field_b IN (SELECT my_function(field_c))

, but if your function returns a complex type, the former style is preferrable, since you can compare to one field of the complex type and return the other within one query, like this:

FUNCTION my_function RETURNS SETOF anothertable

/* This will return a set of records of same layout as in table "anothertable" */

SELECT  field_a, field_b, fc.column1, fc.column2
FROM    (
        SELECT  field_a, field_b, my_function(field_c) fc
        FROM    mytable
        ) q
WHERE   field_b = fc.column1

We compare with column1 here and return both column1 and column2.

This would be impossible with a correlated subquery.