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, ',', '","')|| '"'));
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, ',', '","')|| '"'));
this example is very classic - I newer see ugly code and dirty data elsewhere than on Oracle
SELECT upc.is_mil,
TRIM(column_value) src
FROM user_pool_clean upc,
FROM user_pool_clean upc,
unnest(string_to_array(replace(my_auds,e'\n',''), ',')) column_value;
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
SELECT upc.is_mil,
TRIM(column_value) src
FROM user_pool_clean upc,
FROM user_pool_clean upc,
regexp_split_to_table(replace(my_auds,e'\n',''), ',') column_value;
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
Regards
Pavel
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
