sqlpostgresqlexiststable-functions

Check existence of given text in a table

I have a course code name COMP2221.

I also have a function finder(int) that can find all codes matching a certain pattern.

Like:

select * from finder(20004)

will give:

comp2211
comp2311
comp2411
comp2221

which match the pattern comp2###.

My question is how to express "whether comp2221 is in finder(20004)" in a neat way?

Solution

  • How to express "whether comp2221 is in finder(20004)" in a neat way?

    Use an EXISTS expression and put the test into the WHERE clause:

    SELECT EXISTS (SELECT FROM finder(20004) AS t(code) WHERE code = 'comp2221');

    Returns a single TRUE or FALSE. Never NULL and never more than one row - even if your table function finder() returns duplicates.

    Or fork the function finder() to integrate the test directly. Probably faster.