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;