How can I write a SELECT statement in which a 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 it possible?

my_function would have to return an array?

I am 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 to be SETOF(eg SETOF INT, SETOF VARCHARetc.)

Note that you can use a style expression INas follows:

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 old style is preferable, since you can compare with one field of a complex type and return another in one request, for example:

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

column1 column1, column2.

.

Answer 2

, my_function return type - , field_b (, Varchar (20) int), .