Re: How to perform text merge

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;