Why is PostgreSQL `coalesce` not working as expected/hoped inside a function?
0
votes
1
answer
143
views
A simple function that looks like this:
CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text)
RETURNS tsvector
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
RETURN
to_tsvector(config, coalesce(regexp_replace(source, '\W', ' ', 'g'), ''));
And an even simpler query:
select
text_to_tsvector('english', '123'),
text_to_tsvector('english', ''),
to_tsvector('english', coalesce(regexp_replace(null, '\W', ' ', 'g'), '')),
text_to_tsvector('english', null)
Results in this:
| text_to_tsvector | text_to_tsvector | to_tsvector | text_to_tsvector|
| ---------------- | ---------------- | ----------- | ------------|
| '123':1 | | | null |
Take a note on the last column.
[A fiddle is here](https://www.db-fiddle.com/f/b4qwysK8pUwwwckZawKXNX/3)
What am I missing?
Asked by Kasbolat Kumakhov
(101 rep)
Oct 15, 2023, 12:55 PM
Last activity: Oct 15, 2023, 01:34 PM
Last activity: Oct 15, 2023, 01:34 PM