Check existence of given text in a table

Question

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?

What do you mean "how to interpret "whether comp2221 is in finder(20004)" in a neat way?" What are you trying to do? — JNevill, May 02 '18 at 14:58
Like I want to write `if(comp2221 IN select * from finder(20004))` but it is in wrong syntax, how to express it in plpgsql way — Lilac Liu, May 02 '18 at 15:04

Erwin Brandstetter · Accepted Answer · 2018-05-03T12:57:31.097

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.

1 Answers1