Re: How to perform text merge

In article <609BF3CE079445569FC0D047A5C816AD(at)andrusnotebook>,
"Andrus" <kobruleht2(at)hot(dot)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;