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!
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
