Re: Using XMLNAMESPACES with XMLEMENT

[Garfield Lewis]

Thx @Pavel Stehule, I’ll see if I can figure 
this out…  ☺

Regards,
Garfield

From: Pavel Stehule 
Date: Friday, September 24, 2021 at 11:33 PM
To: Garfield Lewis 
Cc: "pgsql-gene...@postgresql.org" 
Subject: Re: Using XMLNAMESPACES with XMLEMENT

Hi

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis 
mailto:garfield.le...@lzlabs.com>> napsal:
Hi All,

I am attempting to port the following statement from DB2z to Postgres:


SELECT e.empno, e.firstnme, e.lastname,

  XMLELEMENT ( NAME "foo:Emp",

XMLNAMESPACES('http://www.foo.com' AS "foo"),

XMLATTRIBUTES(e.empno as "serial"),

  e.firstnme,

  e.lastname ) AS "Result"

   FROM EMP e

   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT 
function. Is there any way to get this to work? I’ve looked at the WITH syntax 
but it doesn’t look like that will be helpful here.

I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can be 
used only in XMLTABLE function. You need to make XML and in the next step you 
need to modify it as string with string operation.

It can be an interesting feature, and if it is supported by libxml2, then it 
can be easily implemented. But at this moment it is unsupported, and you have 
to use string operations - it should not be hard to use regexp.

Regards

Pavel




Regards,
Garfield

Re: pg_upgrade problem as locale difference in data centers

[Yi Sun]

Hi Tom,

Thank you for your help.

As we use ansible to deploy the upgrade, so mentioned the data centers
situation. The PostgreSQL is single node and the upgrade will be in the
same data center and same Linux server(Centos 7), just will run the ansible
to upgrade PG in all Data centers.

For example, in our aaa data center

postgres=# select datname,datcollate,datctype from pg_database;
datname| datcollate  |  datctype
---+-+-
 postgres   |  ru_RU.UTF-8 |  ru_RU.UTF-8
 template1 |  ru_RU.UTF-8 |  ru_RU.UTF-8
 template0 |  ru_RU.UTF-8 |  ru_RU.UTF-8
 aaa_service  |  ru_RU.UTF-8 |  ru_RU.UTF-8

we test before pg_upgrade, update the postgres, template1, template0 3
databases locale to 'en_US.UTF-8' both in PG9.6 and PG13 as below script

update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname in ('postgres','template1','template0') and
(datcollate<>'en_US.UTF-8' or datctype<>'en_US.UTF-8')

Then test pg_upgrade completed with no error and Locale is like below:

postgres=# select datname,datcollate,datctype from pg_database;
datname| datcollate  |  datctype
---+-+-
 postgres   |  en_US.UTF-8 |  en_US.UTF-8
 template1 |  en_US.UTF-8 |  en_US.UTF-8
 template0 |  en_US.UTF-8 |  en_US.UTF-8
 aaa_service  |  ru_RU.UTF-8 |  ru_RU.UTF-8

The aaa_service application database Locale is no change, and there is no
user-defined table or index in postgres, template1, template0 3 databases.
So the question is like our case, if update the postgres, template1,
template0 3 databases locale and upgrade this way has problems and risk? If
we have to get away with it and choose pg_dump-and-restore or pglogical?
Thanks

Best regards
Sun Yi

Re: Using XMLNAMESPACES with XMLEMENT

[Pavel Stehule]

Hi

ne 26. 9. 2021 v 21:48 odesílatel Garfield Lewis 
napsal:

> Thx @Pavel Stehule , I’ll see if I can figure
> this out…  ☺
>
>
>
> Regards,
>
> Garfield
>
>
>
> *From: *Pavel Stehule 
> *Date: *Friday, September 24, 2021 at 11:33 PM
> *To: *Garfield Lewis 
> *Cc: *"pgsql-gene...@postgresql.org" 
> *Subject: *Re: Using XMLNAMESPACES with XMLEMENT
>
>
>
> Hi
>
>
>
> pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis <
> garfield.le...@lzlabs.com> napsal:
>
> Hi All,
>
>
>
> I am attempting to port the following statement from DB2z to Postgres:
>
>
>
> SELECT e.empno, e.firstnme, e.lastname,
>
>   XMLELEMENT ( NAME "foo:Emp",
>
> XMLNAMESPACES('http://www.foo.com' AS "foo"),
>
> XMLATTRIBUTES(e.empno as "serial"),
>
>   e.firstnme,
>
>   e.lastname ) AS "Result"
>
>FROM EMP e
>
>WHERE e.edlevel = 12;
>
>
>
> The NAMESPACES function is not supported by Postgres in the XMLELEMENT
> function. Is there any way to get this to work? I’ve looked at the WITH
> syntax but it doesn’t look like that will be helpful here.
>
>
>
> I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can
> be used only in XMLTABLE function. You need to make XML and in the next
> step you need to modify it as string with string operation.
>
>
>
> It can be an interesting feature, and if it is supported by libxml2, then
> it can be easily implemented. But at this moment it is unsupported, and you
> have to use string operations - it should not be hard to use regexp.
>

libxml2 supports it - there is an function xmlTextWriterStartElementNS

Postgres supports only the most old version of standard in this area based
on ANSI/SQL 2003. This feature was implemented later, maybe in 2006 or
2008. Postgres cannot support more modern standards because used library
libxml2 doesn't support XQuery, and there is not any other free (with BSD
licence C library). But this feature can be supported, and can be
interesting. The implementation probably cannot be trivial, because SQL
feature cannot be mapped 1:1 to libxml2 feature, but it is implementable.

I found a workaround - you can use xmlattribute instead - it is same like
in Oracle
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Oracle-Compliance-with-SQLXML2011.html#GUID-0D0F19C8-0FB7-4FDD-A55B-18839F340E17
(X080, Namespaces in XML publishing).

postgres=# select xmlelement(name "foo:Emp", XMLATTRIBUTES('
http://www.foo.com' as "xmlns:foo"), xmlelement(name "foo:name", 'Pavel'));
┌──┐
│  xmlelement
   │
╞══╡
│ http://www.foo.com";>Pavel
│
└──┘
(1 row)







>
>
> Regards
>
>
>
> Pavel
>
>
>
>
>
>
>
>
>
> Regards,
>
> Garfield
>
>