In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation? - Mailing list pgsql-general

Hello,

So I have am playing with a view to test the feasibility of a
technique for storing some data.

It basically goes something like this:

CREATE VIEW formatted_table AS
       SELECT name,
          replace(some_template, '@', some_type) AS some_field
         FROM some_table;

some_template is something like 'foo@bar' or 'foobar' (note the
missing template character).

some_type is a single letter like 'a' or 'b', or it can be NULL.

The above view works fine for rows where some_type is a letter, and
some_field ends up as 'fooabar' or whatever.

However, when some_type is NULL, some_field ends up as NULL as well.
I understand that this is expected behaviour, but how do I cause the
view to treat a some_type of NULL as an empty string, so that
some_field simply ends up as 'foobar'?

Hope that was clear.