Why do regexp_matches for any matching lines work like a filter and not return?

Question

I would like to understand why

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

will only return rows where the Atemplate has it pattern, although I have no suggestion WHERE?

What returns regexp_matchesif there is no match? I thought it was null, but if it is null, then the query should just return nullfor all those inappropriate results?

How to create custom functions to perform similar actions: when the argument is appropriate, the corresponding lines are filtered, so I do not need to add WHERE?

Answer 1

Set Returning . FROM. ( ), . .

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)

, . :

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)