From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Dilip Kumar <dilipbalaut(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Help needed with XMLTABLE |
Date: | 2020-04-15 07:25:53 |
Message-ID: | CAFj8pRCDaBCkmQ8+3f4LbHsz3rH54oQ+cW-9O5Dnq0EX4RaYaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut(at)gmail(dot)com> 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(at)fter(dot)dbrefresh(dot)mil
> ',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
>
> insert into user_pool_clean
> values('995CECDC1905375DE05312A270C7CF56','10015848','
> noemail(at)fter(dot)dbrefresh(dot)mil',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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2020-04-15 07:40:37 | Re: Help needed with XMLTABLE |
Previous Message | Pavel Stehule | 2020-04-15 07:11:21 | Re: Help needed with XMLTABLE |