Sample Header Ad - 728x90

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