/* * Install the extension and define the tables. * All the foreign tables defined refer to the same Oracle table. */ SET client_min_messages = WARNING; CREATE EXTENSION oracle_fdw; -- TWO_TASK or ORACLE_HOME and ORACLE_SID must be set in the server's environment for this to work CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '', isolation_level 'read_committed', nchar 'true', set_timezone 'true'); CREATE USER MAPPING FOR CURRENT_ROLE SERVER oracle OPTIONS (user 'SCOTT', password 'tiger'); -- drop the Oracle objects if they exist DO $$BEGIN SELECT oracle_execute('oracle', 'DROP VIEW scott.ttv'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE scott.typetest1 PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP MATERIALIZED VIEW scott.mattest2'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE scott.typetest2 PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; DO $$BEGIN SELECT oracle_execute('oracle', 'DROP TABLE scott.gis PURGE'); EXCEPTION WHEN OTHERS THEN NULL; END;$$; SELECT oracle_execute( 'oracle', E'CREATE TABLE scott.typetest1 (\n' ' id NUMBER(5)\n' ' CONSTRAINT typetest1_pkey PRIMARY KEY,\n' ' c CHAR(10 CHAR),\n' ' nc NCHAR(10),\n' ' vc VARCHAR2(10 CHAR),\n' ' nvc NVARCHAR2(10),\n' ' lc CLOB,\n' ' r RAW(10),\n' ' u RAW(16),\n' ' lb BLOB,\n' ' lr LONG RAW,\n' ' b NUMBER(1),\n' ' num NUMBER(7,5),\n' ' fl BINARY_FLOAT,\n' ' db BINARY_DOUBLE,\n' ' d DATE,\n' ' ts TIMESTAMP WITH TIME ZONE,\n' ' ids INTERVAL DAY TO SECOND,\n' ' iym INTERVAL YEAR TO MONTH\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'CREATE VIEW scott.ttv AS\n' 'SELECT id, vc FROM scott.typetest1' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'CREATE TABLE scott.typetest2 (\n' ' id NUMBER(5)\n' ' CONSTRAINT typetest2_pkey PRIMARY KEY,\n' ' ts1 TIMESTAMP WITH LOCAL TIME ZONE,\n' ' ts2 TIMESTAMP WITH LOCAL TIME ZONE,\n' ' ts3 TIMESTAMP WITH LOCAL TIME ZONE\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'CREATE TABLE scott.gis (\n' ' id NUMBER(5) PRIMARY KEY,\n' ' g MDSYS.SDO_GEOMETRY\n' ') SEGMENT CREATION IMMEDIATE' ); oracle_execute ---------------- (1 row) -- gather statistics SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''TYPETEST1'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''TYPETEST2'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) SELECT oracle_execute( 'oracle', E'BEGIN\n' ' DBMS_STATS.GATHER_TABLE_STATS (''SCOTT'', ''GIS'', NULL, 100);\n' 'END;' ); oracle_execute ---------------- (1 row) -- initial data for typetest2 SELECT oracle_execute( 'oracle', E'INSERT INTO scott.typetest2 (id, ts1, ts2, ts3) VALUES (\n' ' 1,\n' ' FROM_TZ(CAST (''2002-08-01 00:00:00 AD'' AS timestamp), ''UTC''),\n' ' FROM_TZ(CAST (''2002-08-01 00:00:00 AD'' AS timestamp), ''UTC''),\n' ' FROM_TZ(CAST (''2002-08-01 00:00:00 AD'' AS timestamp), ''UTC'')\n' ')' ); oracle_execute ---------------- (1 row) -- a materialized view SELECT oracle_execute( 'oracle', E'CREATE MATERIALIZED VIEW scott.mattest2 REFRESH COMPLETE AS\n' ' SELECT id, ts1, ts2, ts3 FROM scott.typetest2' ); oracle_execute ---------------- (1 row) -- create the foreign tables CREATE FOREIGN TABLE typetest1 ( id integer OPTIONS (key 'yes') NOT NULL, q double precision, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval ) SERVER oracle OPTIONS (table 'TYPETEST1', prefetch '2', lob_prefetch '5000'); ALTER FOREIGN TABLE typetest1 DROP q; -- a table that is missing some fields CREATE FOREIGN TABLE shorty ( id integer OPTIONS (key 'yes') NOT NULL, c character(10) ) SERVER oracle OPTIONS (table 'TYPETEST1'); -- a table that has some extra fields CREATE FOREIGN TABLE longy ( id integer OPTIONS (key 'yes') NOT NULL, c character(10), nc character(10), vc character varying(10), nvc character varying(10), lc text, r bytea, u uuid, lb bytea, lr bytea, b boolean, num numeric(7,5), fl float, db double precision, d date, ts timestamp with time zone, ids interval, iym interval, x integer ) SERVER oracle OPTIONS (table 'TYPETEST1'); CREATE FOREIGN TABLE typetest2 ( id integer OPTIONS (key 'yes') NOT NULL, ts1 timestamp with time zone, ts2 timestamp without time zone, ts3 date ) SERVER oracle OPTIONS (table 'TYPETEST2'); /* * INSERT some rows into "typetest1". */ -- will fail with a read-only transaction ALTER SERVER oracle OPTIONS (SET isolation_level 'read_only'); SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row) DELETE FROM typetest1; ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction -- use the default SERIALIZABLE isolation level from now on ALTER SERVER oracle OPTIONS (DROP isolation_level); SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row) DELETE FROM typetest1; INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 1, 'fixed char', 'nat''l char', 'varlena', 'nat''l var', 'character large object', bytea('\xDEADBEEF'), uuid('055e26fa-f1d8-771f-e053-1645990add93'), bytea('\xDEADBEEF'), bytea('\xDEADBEEF'), TRUE, 3.14159, 3.14159, 3.14159, '1968-10-20', '2009-01-26 15:02:54.893532 PST', '1 day 2 hours 30 seconds 1 microsecond', '-6 months' ); -- change the "boolean" in Oracle to "2" SELECT oracle_execute('oracle', 'UPDATE typetest1 SET b = 2 WHERE id = 1'); oracle_execute ---------------- (1 row) INSERT INTO shorty (id, c) VALUES (2, NULL); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 3, E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb', E'a\u001B\u0007\u000D\u007Fb ABC' || repeat('X', 9000), bytea('\xDEADF00D'), uuid('055f3b32-a02c-4532-e053-1645990a6db2'), bytea('\xDEADF00DDEADF00DDEADF00D'), bytea('\xDEADF00DDEADF00DDEADF00D'), FALSE, -2.71828, -2.71828, -2.71828, '0044-03-15 BC', '0044-03-15 12:00:00 BC', '-2 days -12 hours -30 minutes', '-2 years -6 months' ); INSERT INTO typetest1 (id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym) VALUES ( 4, 'short', 'short', 'short', 'short', 'short', bytea('\xDEADF00D'), uuid('0560ee34-2ef9-1137-e053-1645990ac874'), bytea('\xDEADF00D'), bytea('\xDEADF00D'), NULL, 0, 0, 0, NULL, NULL, '23:59:59.999999', '3 years' ); -- try inserting an empty string into a CLOB (will become NULL) BEGIN; INSERT INTO typetest1 (id, lc) VALUES (5, ''); SELECT lc IS NULL FROM typetest1 WHERE id = 5; ?column? ---------- t (1 row) ROLLBACK; /* * Test SELECT, UPDATE ... RETURNING, DELETE and transactions. */ -- simple SELECT SELECT id, c, nc, vc, nvc, lc, r, u, lb, lr, b, num, fl, db, d, ts, ids, iym, x FROM longy ORDER BY id; WARNING: column number 19 of foreign table "longy" does not exist in foreign Oracle table, will be replaced by NULL id | c | nc | vc | nvc | lc | r | u | lb | lr | b | num | fl | db | d | ts | ids | iym | x ----+----------------------+----------------------+------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------------------------------------+----------------------------+----------------------------+---+----------+-------------+----------+---------------+-------------------------------------+-----------------------------------+----------------------+--- 1 | fixed char | nat'l char | varlena | nat'l var | character large object | \xdeadbeef | 055e26fa-f1d8-771f-e053-1645990add93 | \xdeadbeef | \xdeadbeef | t | 3.14159 | 3.14159012 | 3.14159 | 10-20-1968 | Mon Jan 26 15:02:54.893532 2009 PST | @ 1 day 2 hours 30.000001 secs | @ 6 mons ago | 2 | | | | | | | | | | | | | | | | | | 3 | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb | a\x1B\x07\r\x7Fb ABCXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | \xdeadf00d | 055f3b32-a02c-4532-e053-1645990a6db2 | \xdeadf00ddeadf00ddeadf00d | \xdeadf00ddeadf00ddeadf00d | f | -2.71828 | -2.71828008 | -2.71828 | 03-15-0044 BC | Fri Mar 15 12:00:00 0044 PST BC | @ 2 days 12 hours 30 mins ago | @ 2 years 6 mons ago | 4 | short | short | short | short | short | \xdeadf00d | 0560ee34-2ef9-1137-e053-1645990ac874 | \xdeadf00d | \xdeadf00d | | 0.00000 | 0 | 0 | | | @ 23 hours 59 mins 59.999999 secs | @ 3 years | (4 rows) -- mass UPDATE WITH upd (id, c, lb, d, ts) AS (UPDATE longy SET c = substr(c, 1, 9) || 'u', lb = lb || bytea('\x00'), lr = lr || bytea('\x00'), d = d + 1, ts = ts + '1 day' WHERE id < 3 RETURNING id + 1, c, lb, d, ts) SELECT * FROM upd ORDER BY id; id | c | lb | d | ts ----+------------+--------------+------------+------------------------------------- 2 | fixed chau | \xdeadbeef00 | 10-21-1968 | Tue Jan 27 15:02:54.893532 2009 PST 3 | | | | (2 rows) -- transactions BEGIN; DELETE FROM shorty WHERE id = 2; SAVEPOINT one; -- will cause an error INSERT INTO shorty (id, c) VALUES (1, 'c'); ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-00001: unique constraint (SCOTT.TYPETEST1_PKEY) violated ROLLBACK TO one; INSERT INTO shorty (id, c) VALUES (2, 'c'); ROLLBACK TO one; COMMIT; -- see if the correct data are in the table SELECT id, c FROM typetest1 ORDER BY id; id | c ----+---------------------- 1 | fixed chau 3 | a\x1B\x07\r\x7Fb 4 | short (3 rows) -- try to update the nonexistant column (should cause an error) UPDATE longy SET x = NULL WHERE id = 1; ERROR: no Oracle column modified by UPDATE DETAIL: The UPDATE statement only changes colums that do not exist in the Oracle table. -- check that UPDATES work with "date" in Oracle and "timestamp" in PostgreSQL BEGIN; ALTER FOREIGN TABLE typetest1 ALTER COLUMN d TYPE timestamp(0) without time zone; UPDATE typetest1 SET d = '1968-10-10 12:00:00' WHERE id = 1 RETURNING d; d -------------------------- Thu Oct 10 12:00:00 1968 (1 row) ROLLBACK; -- test if "IN" or "= ANY" expressions are pushed down correctly SELECT id FROM typetest1 WHERE vc = ANY (ARRAY['short', (SELECT 'varlena'::varchar)]) ORDER BY id; id ---- 1 4 (2 rows) EXPLAIN (COSTS off) SELECT id FROM typetest1 WHERE vc = ANY (ARRAY['short', (SELECT 'varlena'::varchar)]) ORDER BY id; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*fdfac5fa5afb2dc8*/ r1."ID", r1."VC" FROM "TYPETEST1" r1 WHERE (r1."VC" IN ('short', :p1)) ORDER BY r1."ID" ASC NULLS LAST InitPlan 1 -> Result (4 rows) -- test modifications that need no foreign scan scan (bug #295) DELETE FROM typetest1 WHERE FALSE; UPDATE shorty SET c = NULL WHERE FALSE RETURNING *; id | c ----+--- (0 rows) -- test deparsing of ScalarArrayOpExpr where the RHS has different element type than the LHS SELECT id FROM typetest1 WHERE vc = ANY ('{zzzzz}'::name[]); id ---- (0 rows) -- test whole-row references with RETURNING (bug #568) INSERT INTO shorty (id, c) VALUES (5, 'return me') RETURNING shorty; shorty ------------------ (5,"return me ") (1 row) UPDATE shorty SET c = 'changed' WHERE id = 5 RETURNING shorty; shorty ------------------ (5,"changed ") (1 row) DELETE FROM shorty WHERE id = 5 RETURNING shorty; shorty ------------------ (5,"changed ") (1 row) -- test generated columns (bug #567) CREATE FOREIGN TABLE gen ( id integer OPTIONS (key 'on') NOT NULL, c character(10) GENERATED ALWAYS AS ('nr ' || id::text) STORED ) SERVER oracle OPTIONS (schema 'SCOTT', table 'TYPETEST1'); INSERT INTO gen (id) VALUES (5); SELECT id, c FROM gen WHERE id = 5; id | c ----+------------ 5 | nr 5 (1 row) UPDATE gen SET id = 6 WHERE id = 5; SELECT id, c FROM gen WHERE id = 6; id | c ----+------------ 6 | nr 6 (1 row) DELETE FROM gen WHERE id = 6; DROP FOREIGN TABLE gen; /* * Test "strip_zeros" column option. */ SELECT oracle_execute( 'oracle', 'INSERT INTO typetest1 (id, vc) VALUES (5, ''has'' || chr(0) || ''zeros'')' ); oracle_execute ---------------- (1 row) SELECT vc FROM typetest1 WHERE id = 5; -- should fail ERROR: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: converting column "vc" for foreign table scan of "typetest1", row 1 ALTER FOREIGN TABLE typetest1 ALTER vc OPTIONS (ADD strip_zeros 'yes'); SELECT vc FROM typetest1 WHERE id = 5; -- should work vc ---------- haszeros (1 row) ALTER FOREIGN TABLE typetest1 ALTER vc OPTIONS (DROP strip_zeros); DELETE FROM typetest1 WHERE id = 5; /* * Test EXPLAIN support. */ EXPLAIN (COSTS off) UPDATE typetest1 SET lc = current_timestamp WHERE id < 4 RETURNING id + 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on typetest1 Oracle statement: UPDATE "TYPETEST1" SET "LC" = :p7 WHERE "ID" = :k1 RETURNING "ID" INTO :r1 -> Foreign Scan on typetest1 Oracle query: SELECT /*5c452aa357a4239f*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 WHERE (r1."ID" < 4) FOR UPDATE (4 rows) EXPLAIN (VERBOSE on, COSTS off) SELECT * FROM shorty; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.shorty Output: id, c Oracle query: SELECT /*92c465781c2259ec*/ r1."ID", r1."C" FROM "TYPETEST1" r1 Oracle plan: SELECT STATEMENT Oracle plan: TABLE ACCESS FULL TYPETEST1 (5 rows) -- this should fetch all columns from the foreign table EXPLAIN (COSTS off) SELECT typetest1 FROM typetest1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*f45af6ff7fe0f072*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 (2 rows) /* * Test parameters. */ PREPARE stmt(integer, date, timestamp, uuid) AS SELECT d FROM typetest1 WHERE id = $1 AND d < $2 AND ts < $3 AND u = $4; -- six executions to switch to generic plan EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) EXPLAIN (COSTS off) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*948d44ca5107f0d1*/ r1."ID", r1."U", r1."D", r1."TS" FROM "TYPETEST1" r1 WHERE (r1."D" < CAST (:p1 AS DATE)) AND (r1."TS" < CAST (:p2 AS TIMESTAMP)) AND (r1."ID" = :p3) AND (r1."U" = :p4) (2 rows) EXECUTE stmt(1, '2011-03-09', '2011-03-09 05:00:00', '055e26fa-f1d8-771f-e053-1645990add93'); d ------------ 10-21-1968 (1 row) DEALLOCATE stmt; -- test NULL parameters SELECT id FROM typetest1 WHERE vc = (SELECT NULL::text); id ---- (0 rows) /* * Test current_timestamp. */ SELECT id FROM typetest1 WHERE d < current_date AND ts < now() AND ts < current_timestamp AND ts < 'now'::timestamp ORDER BY id; id ---- 1 3 (2 rows) /* * Test foreign table based on SELECT statement. */ CREATE FOREIGN TABLE qtest ( id integer OPTIONS (key 'yes') NOT NULL, vc character varying(10), num numeric(7,5) ) SERVER oracle OPTIONS (table '(SELECT id, vc, num FROM typetest1)'); -- INSERT works with simple "view" INSERT INTO qtest (id, vc, num) VALUES (5, 'via query', -12.5); ALTER FOREIGN TABLE qtest OPTIONS (SET table '(SELECT id, SUBSTR(vc, 1, 3), num FROM typetest1)'); -- SELECT and DELETE should also work with derived columns SELECT * FROM qtest ORDER BY id; id | vc | num ----+-----------+----------- 1 | var | 3.14159 3 | a\x1B\x07 | -2.71828 4 | sho | 0.00000 5 | via | -12.50000 (4 rows) DELETE FROM qtest WHERE id = 5; /* * Test COPY */ BEGIN; COPY typetest1 FROM STDIN; ROLLBACK; /* * Test foreign table as a partition. */ CREATE TABLE party (LIKE typetest1) PARTITION BY RANGE (id); CREATE TABLE defpart PARTITION OF party DEFAULT; ALTER TABLE party ATTACH PARTITION typetest1 FOR VALUES FROM (1) TO (MAXVALUE); BEGIN; COPY party FROM STDIN; INSERT INTO party (id, lc, lr, lb) VALUES (12, 'very long character', '\x0001020304', '\xFFFEFDFC'); SELECT id, lr, lb, c FROM typetest1 ORDER BY id; id | lr | lb | c -----+----------------------------+----------------------------+---------------------- 1 | \xdeadbeef00 | \xdeadbeef00 | fixed chau 3 | \xdeadf00ddeadf00ddeadf00d | \xdeadf00ddeadf00ddeadf00d | a\x1B\x07\r\x7Fb 4 | \xdeadf00d | \xdeadf00d | short 12 | \x0001020304 | \xfffefdfc | 666 | \xffff | \x01020304 | cöpy 777 | \x00 | \x00 | fdjkl (6 rows) ROLLBACK; BEGIN; CREATE TABLE shortpart ( id integer NOT NULL, c character(10) ) PARTITION BY LIST (id); ALTER TABLE shortpart ATTACH PARTITION shorty FOR VALUES IN (1, 2, 3, 4, 5, 6, 7, 8, 9); INSERT INTO shortpart (id, c) VALUES (6, 'returnme') RETURNING *; id | c ----+------------ 6 | returnme (1 row) ROLLBACK; /* * Test triggers on foreign tables. */ -- trigger function CREATE FUNCTION shorttrig() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF TG_OP IN ('UPDATE', 'DELETE') THEN RAISE WARNING 'trigger % % OLD row: id = %, c = %', TG_WHEN, TG_OP, OLD.id, OLD.c; END IF; IF TG_OP IN ('INSERT', 'UPDATE') THEN RAISE WARNING 'trigger % % NEW row: id = %, c = %', TG_WHEN, TG_OP, NEW.id, NEW.c; END IF; NEW.c := 'modified'; RETURN NEW; END;$$; -- test BEFORE trigger CREATE TRIGGER shorttrig BEFORE UPDATE ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; UPDATE shorty SET id = id + 1 WHERE id = 4 RETURNING c; WARNING: trigger BEFORE UPDATE OLD row: id = 4, c = short WARNING: trigger BEFORE UPDATE NEW row: id = 5, c = short c ------------ modified (1 row) ROLLBACK; -- test AFTER trigger DROP TRIGGER shorttrig ON shorty; CREATE TRIGGER shorttrig AFTER UPDATE ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; UPDATE shorty SET id = id + 1 WHERE id = 4; WARNING: trigger AFTER UPDATE OLD row: id = 4, c = short WARNING: trigger AFTER UPDATE NEW row: id = 5, c = short ROLLBACK; -- test AFTER INSERT trigger with COPY DROP TRIGGER shorttrig ON shorty; CREATE TRIGGER shorttrig AFTER INSERT ON shorty FOR EACH ROW EXECUTE PROCEDURE shorttrig(); BEGIN; COPY shorty FROM STDIN; WARNING: trigger AFTER INSERT NEW row: id = 42, c = hammer WARNING: trigger AFTER INSERT NEW row: id = 753, c = rom WARNING: trigger AFTER INSERT NEW row: id = 0, c = ROLLBACK; /* * Test ORDER BY pushdown. */ -- don't push down string data types EXPLAIN (COSTS off) SELECT id FROM typetest1 ORDER BY id, vc; QUERY PLAN ---------------------------------------------------------------------------------------- Sort Sort Key: id, vc -> Foreign Scan on typetest1 Oracle query: SELECT /*5481fa8e920c0b1e*/ r1."ID", r1."VC" FROM "TYPETEST1" r1 (4 rows) -- push down complicated expressions EXPLAIN (COSTS off) SELECT id FROM typetest1 ORDER BY length(vc), CASE WHEN vc IS NULL THEN 0 ELSE 1 END, ts DESC NULLS FIRST FOR UPDATE; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- LockRows -> Foreign Scan on typetest1 Oracle query: SELECT /*5074cc14d3a68fac*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 ORDER BY length(r1."VC") ASC NULLS LAST, CASE WHEN (r1."VC" IS NULL) THEN 0 ELSE 1 END ASC NULLS LAST, r1."TS" DESC NULLS FIRST FOR UPDATE (3 rows) SELECT id FROM typetest1 ORDER BY length(vc), CASE WHEN vc IS NULL THEN 0 ELSE 1 END, ts DESC NULLS FIRST FOR UPDATE; id ---- 4 3 1 (3 rows) /* * Test that incorrect type mapping throws an error. */ -- create table with bad type matches CREATE FOREIGN TABLE badtypes ( id integer OPTIONS (key 'yes') NOT NULL, c xml, nc xml ) SERVER oracle OPTIONS (table 'TYPETEST1'); -- should fail for column "nc", as "c" is not used SELECT id, nc FROM badtypes WHERE id = 1; ERROR: column "nc" (142) of foreign table "badtypes" cannot be converted to or from Oracle data type (1) -- this will fail for inserting a NULL in column "c" INSERT INTO badtypes (id, nc) VALUES (42, XML ''); ERROR: column "c" (142) of foreign table "badtypes" cannot be converted to or from Oracle data type (1) -- remove foreign table DROP FOREIGN TABLE badtypes; /* * Test subplans (initplans) */ -- testcase for bug #364 SELECT id FROM typetest1 WHERE vc NOT IN (SELECT * FROM (VALUES ('short'), ('other')) AS q) ORDER BY id; id ---- 1 3 (2 rows) /* * Test type coerced array parameters (bug #452) */ PREPARE stmt(varchar[]) AS SELECT id FROM typetest1 WHERE vc = ANY ($1); EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) EXECUTE stmt('{varlena,nonsense}'); id ---- 1 (1 row) DEALLOCATE stmt; /* * Test push-down of the LIMIT clause. */ -- the LIMIT clause is only pushed down with ORDER BY EXPLAIN (COSTS off) SELECT d FROM typetest1 LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------ Limit -> Foreign Scan on typetest1 Oracle query: SELECT /*86c38fa86d962698*/ r1."D" FROM "TYPETEST1" r1 (3 rows) EXPLAIN (COSTS off) SELECT d FROM typetest1 ORDER BY d LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Limit -> Foreign Scan on typetest1 Oracle query: SELECT /*e576e014fab5be1c*/ r1."D" FROM "TYPETEST1" r1 ORDER BY r1."D" ASC NULLS LAST FETCH FIRST 2 ROWS ONLY (3 rows) SELECT d FROM typetest1 ORDER BY d LIMIT 2; d --------------- 03-15-0044 BC 10-21-1968 (2 rows) -- the LIMIT clause is not pushed down because the ORDER BY is not EXPLAIN (COSTS off) SELECT d FROM typetest1 ORDER BY lc LIMIT 2; QUERY PLAN --------------------------------------------------------------------------------------------- Limit -> Sort Sort Key: lc -> Foreign Scan on typetest1 Oracle query: SELECT /*4774138fc3e1aafa*/ r1."LC", r1."D" FROM "TYPETEST1" r1 (5 rows) -- with an OFFSET clause, the offset value is added to the limit EXPLAIN (COSTS off) SELECT * FROM qtest ORDER BY id LIMIT 1 OFFSET 2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit -> Foreign Scan on qtest Oracle query: SELECT /*ebe0d0f6395ace30*/ r1."ID", r1."SUBSTR(VC,1,3)", r1."NUM" FROM (SELECT id, SUBSTR(vc, 1, 3), num FROM typetest1) r1 ORDER BY r1."ID" ASC NULLS LAST FETCH FIRST 1+2 ROWS ONLY (3 rows) SELECT * FROM qtest ORDER BY id LIMIT 1 OFFSET 2; id | vc | num ----+-----+--------- 4 | sho | 0.00000 (1 row) -- no LIMIT push-down if there is a GROUP BY clause EXPLAIN (COSTS off) SELECT d, count(*) FROM typetest1 GROUP BY d LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit -> GroupAggregate Group Key: d -> Foreign Scan on typetest1 Oracle query: SELECT /*3ad9a4ae16c8af72*/ r1."D" FROM "TYPETEST1" r1 ORDER BY r1."D" ASC NULLS LAST (5 rows) SELECT d, count(*) FROM typetest1 GROUP BY d LIMIT 2; d | count ---------------+------- 03-15-0044 BC | 1 10-21-1968 | 1 (2 rows) -- no LIMIT push-down if there is an aggregate function EXPLAIN (COSTS off) SELECT 12 - count(*) FROM typetest1 LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------- Limit -> Aggregate -> Foreign Scan on typetest1 Oracle query: SELECT /*5ac4525c1631b53c*/ '1' FROM "TYPETEST1" r1 (4 rows) SELECT 12 - count(*) FROM typetest1 LIMIT 1; ?column? ---------- 9 (1 row) -- no LIMIT push-down if there is a local WHERE condition EXPLAIN (COSTS OFF) SELECT id FROM typetest1 WHERE vc < 'u' LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------- Limit -> Foreign Scan on typetest1 Filter: ((vc)::text < 'u'::text) Oracle query: SELECT /*5481fa8e920c0b1e*/ r1."ID", r1."VC" FROM "TYPETEST1" r1 (4 rows) SELECT id FROM typetest1 WHERE vc < 'u' LIMIT 1; id ---- 3 (1 row) -- no LIMIT pushdown with FOR SHARE/UPDATE EXPLAIN (COSTS OFF) SELECT id FROM typetest1 ORDER BY id LIMIT 1 FOR UPDATE; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit -> LockRows -> Foreign Scan on typetest1 Oracle query: SELECT /*acef6247e022b52b*/ r1."ID", r1."C", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 ORDER BY r1."ID" ASC NULLS LAST FOR UPDATE (4 rows) /* test ANALYZE */ ANALYZE typetest1; ANALYZE longy; -- bug reported by Jan ANALYZE shorty; /* test if views and SECURITY DEFINER functions use the correct user mapping */ CREATE ROLE duff LOGIN; GRANT SELECT ON typetest1 TO PUBLIC; CREATE VIEW v_typetest1 AS SELECT id FROM typetest1; GRANT SELECT ON v_typetest1 TO PUBLIC; CREATE VIEW v_join AS SELECT id, a.vc, b.c FROM typetest1 AS a JOIN typetest1 AS b USING (id); GRANT SELECT ON v_join TO PUBLIC; CREATE FUNCTION f_typetest1() RETURNS TABLE (id integer) LANGUAGE sql SECURITY DEFINER AS 'SELECT id FROM public.typetest1'; SET SESSION AUTHORIZATION duff; -- this should fail SELECT id FROM typetest1 ORDER BY id; ERROR: user mapping not found for user "duff", server "oracle" -- these should succeed SELECT id FROM v_typetest1 ORDER BY id; id ---- 1 3 4 (3 rows) SELECT c FROM v_join WHERE vc = 'short'; c ------------ short (1 row) SELECT id FROM f_typetest1() ORDER BY id; id ---- 1 3 4 (3 rows) -- clean up RESET SESSION AUTHORIZATION; DROP ROLE duff; /* test "current_timestamp" and "current_date" pushdown */ EXPLAIN (COSTS off) SELECT id FROM typetest1 WHERE ts = current_timestamp; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*32d2aac8fef06c7f*/ r1."ID", r1."TS" FROM "TYPETEST1" r1 WHERE (r1."TS" = (CAST (:now AS TIMESTAMP WITH TIME ZONE))) (2 rows) SELECT id FROM typetest1 WHERE ts = current_timestamp; id ---- (0 rows) EXPLAIN (COSTS off) SELECT id FROM typetest1 WHERE d = current_date; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on typetest1 Oracle query: SELECT /*50cc2b05999777b5*/ r1."ID", r1."D" FROM "TYPETEST1" r1 WHERE (r1."D" = TRUNC(CAST (CAST(:now AS TIMESTAMP WITH TIME ZONE) AS DATE))) (2 rows) SELECT id FROM typetest1 WHERE d = current_date; id ---- (0 rows) /* test TIMESTAMP WITH LOCAL TIME ZONE */ INSERT INTO typetest2 (id, ts1, ts2, ts3) VALUES ( 2, '2020-12-31 00:00:00 UTC', '2020-12-31 00:00:00', '2020-12-31' ); SELECT * FROM typetest2 ORDER BY id; id | ts1 | ts2 | ts3 ----+------------------------------+--------------------------+------------ 1 | Wed Jul 31 17:00:00 2002 PDT | Wed Jul 31 17:00:00 2002 | 07-31-2002 2 | Wed Dec 30 16:00:00 2020 PST | Thu Dec 31 00:00:00 2020 | 12-31-2020 (2 rows) -- we need to re-establish the connection after changing "timezone" SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row) BEGIN; SET LOCAL timezone = 'Asia/Kolkata'; INSERT INTO typetest2 (id, ts1, ts2, ts3) VALUES ( 3, '2020-12-31 00:00:00 UTC', '2020-12-31 00:00:00', '2020-12-31' ); SELECT * FROM typetest2 ORDER BY id; id | ts1 | ts2 | ts3 ----+------------------------------+--------------------------+------------ 1 | Thu Aug 01 05:30:00 2002 IST | Thu Aug 01 05:30:00 2002 | 08-01-2002 2 | Thu Dec 31 05:30:00 2020 IST | Thu Dec 31 13:30:00 2020 | 12-31-2020 3 | Thu Dec 31 05:30:00 2020 IST | Thu Dec 31 00:00:00 2020 | 12-31-2020 (3 rows) COMMIT; -- we need to re-establish the connection after changing "timezone" SELECT oracle_close_connections(); oracle_close_connections -------------------------- (1 row)