From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Ben Morgan <neembi(at)gmail(dot)com> |
Cc: | PostgreSQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: xmlelement name |
Date: | 2013-03-12 14:27:19 |
Message-ID: | CAFj8pRCUfHYF1LUCMZvdr_00B14G_YC5ZFJMEQpV-tS+p5srNA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello
2013/3/12 Ben Morgan <neembi(at)gmail(dot)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
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2013-03-14 15:29:07 | bug in 9.2.2 ? subquery accepts wrong column name : upd |
Previous Message | Ben Morgan | 2013-03-12 13:55:02 | xmlelement name |