Re: How to perform text merge

On 28 Mar 2010, at 19:43, Andrus wrote:

> Database column contains merge data in text column.
> Expressions are between << and >> separators.
> How to replace them with database values ?
>
> For example, code below should return:
>
> Hello Tom Lane!
>
> How to implement textmerge procedure or other idea ?
>
> Andrus.
>
> create temp table person ( firstname text, lastname text ) on commit drop;
> insert into person values ('Tom', 'Lane');
> create temp table mergedata ( template text ) on commit drop;
> insert into mergedata values ('Hello <<firstname||'' ''||lastname>>!');
>
> select textmerge(template,'select * from person') from mergedata;

Since you pretty much invented your own language you're probably best suited with writing your own parser. Have a look at flex/yacc or whatever it's equivalent is on Windows if that has your preference.

If you "dumb it down" a bit by replacing the expressions by simple tokens then you could handle this with regular expressions, for example:

insert into mergedata values ('Hello <<firstname>> <<lastname>>!');

You replace <<firstname>> and <<lastname>> with their respective values using regexp_replace. You'll need to nest a few calls to that function to get the result you want.
You could be a bit smarter about this and create a <<fullname>> macro that you fill from a function result that uses (firstname, lastname) as input parameters and returns firstname || ' ' || lastname. Views are useful for providing such data too.

Personally I think you're using a bad example here, as usually names don't just involve firstname and surname, but frequently have infixes, suffixes and titles and such. Not all of those fields are going to have values for every person in your database. What happens if you don't have a Tom Lane, but a mr. Lane, or if you have both but want to address a person more politely?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4baf9e7810416492686854!