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

From: "Asfand Qazi (Sanger Institute)" <aq2(dot)sanger(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
Date: 2011-06-27 16:53:48
Message-ID: BANLkTinr7YRJRwWGLKcCB9ahVFHVNof8ww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2011-06-27 16:58:07 Re: In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
Previous Message Merlin Moncure 2011-06-27 16:02:03 Re: Custom types as parameter in stored function