Thread: How to perform text merge

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;

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!

> Since you pretty much invented your own language

Expressions are in PostgreSql syntax.

I expected that there is some way to force PostgreSql to evaluate them at
runtime using something like pgsql EXECUTE
For example,

'Hello <<firstname||'' ''||lastname>>!'

should be converted (inverted) to

'Hello ' || firstname||' '||lastname || '!'

and  then pgsql EXECUTE can be used to perform text merge just like .asp
pages are pre-processed and compiled.

How to do this is PostgreSql or in C# in MONO/.NET ?

> 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?

COALESCE(), CASE WHEN  and other pgsql constructs can be used to create
correct address expressions from any data.

Andrus.

In article <609BF3CE079445569FC0D047A5C816AD@andrusnotebook>,
"Andrus" <kobruleht2@hot.ee> writes:

> 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;

Here's a quick shot:

CREATE FUNCTION textmerge(tpl text, query text) RETURNS text AS $$
DECLARE
  pref text = substring(tpl FROM '(.*)<<');
  expr text = substring(tpl FROM '<<(.+)>>');
  post text = substring(tpl FROM '>>(.*)');
  tmp1 text = regexp_replace(query, E'\\*', expr);
  tmp2 text;
BEGIN
  EXECUTE tmp1 INTO tmp2;
  RETURN pref || tmp2 || post;
END;
$$ LANGUAGE plpgsql IMMUTABLE;