Generating fields in views with search/replace?

On Thu, May 5, 2011 at 9:09 AM, Asfand Qazi (Sanger Institute)
<aq2.sanger@gmail.com> wrote:

Hi,

Say I have a table with fields 'template' and 'original_letter', and
'new_selected_letter'.  'template' could be 'abcdefg0abcdefg',
original_letter could be 'A' and new_selected_letter could be 'B'.

I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg'
and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced
the '0' with original_letter or new_selected_letter respectively.

Sorry for the silly example, but is there a way for me to do this in
Postgresql?  I'm using Postgresql 9

The mechanics of making a view do something like that is trivial, but
your example doesn't make clear why the character '0' is special and
is the one translated?

merlin

Asfand Qazi (Sanger Institute) wrote:

Say I have a table with fields 'template' and 'original_letter', and
'new_selected_letter'. 'template' could be 'abcdefg0abcdefg',
original_letter could be 'A' and new_selected_letter could be 'B'.

I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg'
and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced
the '0' with original_letter or new_selected_letter respectively.

Well, in 8.4.7 you'd use something like:

create view xyz as
select regexp_replace(template, '0', original_letter) as original,
regexp_replace(template, '0', new_selected_letter) as new_selected
from template_table;

Should be the same in 9.x.

(See docs for more info on the regexp_replace() function)

HTH.

Later,
Bosco.

On Thu, May 5, 2011 at 8:37 PM, Bosco Rama <postgres@boscorama.com> wrote:

Asfand Qazi (Sanger Institute) wrote:

Say I have a table with fields 'template' and 'original_letter', and
'new_selected_letter'.  'template' could be 'abcdefg0abcdefg',
original_letter could be 'A' and new_selected_letter could be 'B'.

I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg'
and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced
the '0' with original_letter or new_selected_letter respectively.

Well, in 8.4.7 you'd use something like:

  create view xyz as
     select regexp_replace(template, '0', original_letter) as original,
            regexp_replace(template, '0', new_selected_letter) as new_selected
        from template_table;

Should be the same in 9.x.

(See docs for more info on the regexp_replace() function)

HTH.

Later,
Bosco.

Ah.... thanks, that's perfect.

I won't be using a '0', I'll be using some non-printable ASCII
character in the template, but the concept was the same.

Again, many thanks. Regards,
Asfand Qazi