Re: xmlelement name - Mailing list pgsql-sql

From Pavel Stehule
Subject Re: xmlelement name
Date
Msg-id CAFj8pRCUfHYF1LUCMZvdr_00B14G_YC5ZFJMEQpV-tS+p5srNA@mail.gmail.com
Whole thread Raw
In response to xmlelement name  (Ben Morgan <neembi@gmail.com>)
List pgsql-sql
Hello

2013/3/12 Ben Morgan <neembi@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 xmlLANGUAGE 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


>
> 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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



pgsql-sql by date:

Previous
From: Ben Morgan
Date:
Subject: xmlelement name
Next
From: Achilleas Mantzios
Date:
Subject: bug in 9.2.2 ? subquery accepts wrong column name : upd