xmlelement name

[Ben Morgan]

Hi,

I'm trying to write a function that will take a name as a text value,
and return an XML element with that name as name, like so:

create function xpercent(nam text, val int) returns xml as $$
begin
return ( select xmlelement(name nam, concat(val::text, '%')) );
end;
$$ language plpgsql;

But when I call the function, nam is used as the name instead of what
the parameter nam contains:

select xpercent('hello', 4);
xpercent
---------------
<nam>4%</nam>
(1 row)

How can I get this to work so I get <hello>4%</hello instead?
Thanks!

–Ben


--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Pavel Stehule]

Hello

2013/3/12 Ben Morgan <nee...@gmail.com>:

> Hi,
>
> I'm trying to write a function that will take a name as a text value,
> and return an XML element with that name as name, like so:
>
> create function xpercent(nam text, val int) returns xml as $$
> begin
> return ( select xmlelement(name nam, concat(val::text, '%')) );
> end;
> $$ language plpgsql;

you cannout use parameter there - Name of xmlttribute is constant, it
should be immutable

you have to use dynamic sql

CREATE OR REPLACE FUNCTION public.xpercent(nam text, val integer)
RETURNS xml
LANGUAGE plpgsql
AS $function$
declare result text;
begin
execute format('SELECT xmlelement(name %I, $1)', nam) USING
concat(val::text, '%') INTO result;
return result;
end;
$function$

postgres=# select xpercent('hello', 4);
xpercent
-------------------
<hello>4%</hello>
(1 row)


Regards

Pavel Stehule