Search Postgresql Archives

Re: Help needed with XMLTABLE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut@xxxxxxxxx> napsal:
One of our customers tried to use XMLTABLE syntax without
row_expression, which works fine with ORACLE but doesn't work with
PostgreSQL.  So can anyone suggest what alternative we can use?

CREATE TABLE user_pool_clean (
    fk_user_pool_pk bytea NOT NULL,
    user_id character varying(255) NOT NULL,
    email_address character varying(250),
    is_mil numeric,
    is_civ numeric,
    is_ctr numeric,
    is_gov numeric,
    is_edu numeric,
    role_id character varying(50),
    user_profile_id character varying(50),
    service_branch_id character varying(50),
    mil_pay_grade_id character varying(50),
    my_auds character varying(4000),
    my_orgs character varying(4000),
    processed character(1) DEFAULT 'N'::bpchar NOT NULL
);

insert into user_pool_clean
values('995CECDC1881375DE05312A270C7CF56','10015706','noemail@xxxxxxxxxxxxxxxxxx',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');

insert into user_pool_clean
values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@xxxxxxxxxxxxxxxxxx',0,0,0,0,0,'1','3','700015','11','
1705562,1708486','1710621','Y');


SQL> SELECT upc.is_mil,TRIM(column_value) src
         FROM   user_pool_clean upc
               ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));

It's very famous how people are creative - when xmltable has only one argument, then it is evaluated like XQuery _expression_.

https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/

Unfortunately, Postgres has not support of XQuery, so nothing similar is possible. Fortunately, Postgres has much better tools for parsing string.

Regards

Pavel


    IS_MIL SRC
---------- ---------------
     0       1705562              --------O/P from the oracle database
     0        1708486
    0        1706882

postgres[7604]=# SELECT upc.is_mil,TRIM(column_value) src
postgres-#          FROM   user_pool_clean upc
postgres-#                ,xmltable(('"'|| REPLACE( upc.my_auds, ',',
'","')|| '"'));
ERROR:  syntax error at or near ")"
LINE 3: ... ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux