Why regexp_matches for none matching rows work like a filter, and doesn't return?

Question

I would like to understand why

SELECT *, regexp_matches(A, 'pattern') FROM table

would only return rows where A has pattern pattern, even though I don't have WHERE clause?

What does regexp_matches return if there is no match? I had thought it was null, but if it is null then the query should simply return null for all those non matching results?

How can I build custom functions to do similar thing: when the argument fits, the corresponding rows are filtered so that I don't have to append where?

Answer 1

It is a side effect of using Set Returning Function as scalar function. Set Returning Functions returns a table instead scalar function and usually are used in FROM clause. You can use it like scalar function (from some reasons), but with very strange side effects some times. Usually you don't would to use these function on scalar function position.

postgres=# select * from foo;
 a 
---
 1
 2
(2 rows)

postgres=# select a, generate_series(1,0) from foo;
 a | generate_series 
---+-----------------
(0 rows)

postgres=# select a, generate_series(1,1) from foo;
 a | generate_series 
---+-----------------
 1 |               1
 2 |               1
(2 rows)

postgres=# select a, generate_series(1,2) from foo;
 a | generate_series 
---+-----------------
 1 |               1
 1 |               2
 2 |               1
 2 |               2
(4 rows)

Probably you would to use a substring function with regular expression. It is scalar function:

postgres=# select 'Some string', substring('Ahoj29' from '^[0-9]+');
  ?column?   | substring 
-------------+-----------
 Some string | 
(1 row)

postgres=# select 'Some string', substring('Ahoj29' from '[0-9]+');
  ?column?   | substring 
-------------+-----------
 Some string | 29
(1 row)