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
atflex/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
callsto 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
datatoo.
Personally I think you're using a bad example here, as usually names don't just involve firstname and surname, but
frequentlyhave infixes, suffixes and titles and such. Not all of those fields are going to have values for every person
inyour 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
personmore 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!
