topic Re: Recover domains with sql query in a postgresql geodatabase in Geodatabase Questions

Recover domains with sql query in a postgresql geodatabase

Carlos_AlfredoRodriguez — Fri, 17 Aug 2018 00:06:18 GMT

Hi, I need to create this Oracle script in Postgresql, I do not have experience in xml and I need to list all the domains in a query, but in a Postgresql geodatabase. The query below it works in a Oracle Geodatabase. Thanks. Carlos Rodriguez

SELECT items.Name NOMBRE_DOMINIO,
itemtypes.Name TIPO_DOMINIO,
EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS CODIGO_DOMINIO,
EXTRACTVALUE (CodedValues.COLUMN_VALUE, 'CodedValue/Name') AS DESCRIPCION_DOMINIO
FROM SDE.GDB_ITEMS_VW items INNER JOIN SDE.GDB_ITEMTYPES itemtypes
ON items.TYPE = itemtypes.UUID,
TABLE (XMLSEQUENCE (XMLType (Definition).EXTRACT ('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValue
WHERE itemtypes.Name IN ('Coded Value Domain', 'Range Domain');

Re: Recover domains with sql query in a postgresql geodatabase

Carlos_AlfredoRodriguez — Sun, 12 Dec 2021 08:30:26 GMT

It's work with this script:
SELECT name as domain_name, 
unnest(CAST(xpath('//CodedValue/Name/text()',definition) AS text)::text[]) AS description,
unnest(CAST(xpath('//CodedValue/Code/text()',definition) AS text)::text[]) AS code
from sde.gdb_items

Re: Recover domains with sql query in a postgresql geodatabase

AndrewKesterton — Thu, 01 Dec 2022 13:59:26 GMT

It's a few years now, but thank you for sharing the answer to your own question Carlos.

I came across it looking for a postgres equivalent for these examples in the documentation which are only given for Oracle and MySQL:, Example: Resolving domain codes to description values using SQL