CREATE EXTENSION multicorn; CREATE server multicorn_srv foreign data wrapper multicorn options ( wrapper 'multicorn.testfdw.TestForeignDataWrapper' ); CREATE user mapping for postgres server multicorn_srv options (usermapping 'test'); CREATE foreign table testmulticorn ( test1 character varying, test2 character varying ) server multicorn_srv options ( option1 'option1', test_type 'nowrite', tx_hook 'true' ); insert into testmulticorn(test1, test2) VALUES ('test', 'test2'); NOTICE: [('option1', 'option1'), ('test_type', 'nowrite'), ('tx_hook', 'true'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying'), ('test2', 'character varying')] NOTICE: BEGIN NOTICE: ROLLBACK ERROR: Error in python: NotImplementedError DETAIL: This FDW does not support the writable API update testmulticorn set test1 = 'test'; NOTICE: BEGIN NOTICE: [] NOTICE: ['test1', 'test2'] NOTICE: ROLLBACK ERROR: Error in python: NotImplementedError DETAIL: This FDW does not support the writable API delete from testmulticorn where test2 = 'test2 2 0'; NOTICE: BEGIN NOTICE: [test2 = test2 2 0] NOTICE: ['test1', 'test2'] NOTICE: ROLLBACK ERROR: Error in python: NotImplementedError DETAIL: This FDW does not support the writable API CREATE foreign table testmulticorn_write ( test1 character varying, test2 character varying ) server multicorn_srv options ( option1 'option1', row_id_column 'test1', test_type 'returning', tx_hook 'true' ); insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2'); NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('tx_hook', 'true'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying'), ('test2', 'character varying')] NOTICE: BEGIN NOTICE: INSERTING: [('test1', u'test'), ('test2', u'test2')] NOTICE: PRECOMMIT NOTICE: COMMIT update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%'; NOTICE: BEGIN NOTICE: [test1 ~~* test1 3%] NOTICE: ['test1', 'test2'] NOTICE: UPDATING: test1 3 1 with [('test1', u'test'), ('test2', u'test2 1 1')] NOTICE: UPDATING: test1 3 4 with [('test1', u'test'), ('test2', u'test2 1 4')] NOTICE: UPDATING: test1 3 7 with [('test1', u'test'), ('test2', u'test2 1 7')] NOTICE: UPDATING: test1 3 10 with [('test1', u'test'), ('test2', u'test2 1 10')] NOTICE: UPDATING: test1 3 13 with [('test1', u'test'), ('test2', u'test2 1 13')] NOTICE: UPDATING: test1 3 16 with [('test1', u'test'), ('test2', u'test2 1 16')] NOTICE: UPDATING: test1 3 19 with [('test1', u'test'), ('test2', u'test2 1 19')] NOTICE: PRECOMMIT NOTICE: COMMIT delete from testmulticorn_write where test2 = 'test2 2 0'; NOTICE: BEGIN NOTICE: [test2 = test2 2 0] NOTICE: ['test1', 'test2'] NOTICE: DELETING: test1 1 0 NOTICE: PRECOMMIT NOTICE: COMMIT -- Test returning insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2') RETURNING test1; NOTICE: BEGIN NOTICE: INSERTING: [('test1', u'test'), ('test2', u'test2')] NOTICE: PRECOMMIT NOTICE: COMMIT test1 ---------------- INSERTED: test (1 row) update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%' RETURNING test1; NOTICE: BEGIN NOTICE: [test1 ~~* test1 3%] NOTICE: ['test1', 'test2'] NOTICE: UPDATING: test1 3 1 with [('test1', u'test'), ('test2', u'test2 1 1')] NOTICE: UPDATING: test1 3 4 with [('test1', u'test'), ('test2', u'test2 1 4')] NOTICE: UPDATING: test1 3 7 with [('test1', u'test'), ('test2', u'test2 1 7')] NOTICE: UPDATING: test1 3 10 with [('test1', u'test'), ('test2', u'test2 1 10')] NOTICE: UPDATING: test1 3 13 with [('test1', u'test'), ('test2', u'test2 1 13')] NOTICE: UPDATING: test1 3 16 with [('test1', u'test'), ('test2', u'test2 1 16')] NOTICE: UPDATING: test1 3 19 with [('test1', u'test'), ('test2', u'test2 1 19')] NOTICE: PRECOMMIT NOTICE: COMMIT test1 --------------- UPDATED: test UPDATED: test UPDATED: test UPDATED: test UPDATED: test UPDATED: test UPDATED: test (7 rows) delete from testmulticorn_write where test1 = 'test1 1 0' returning test2, test1; NOTICE: BEGIN NOTICE: [test1 = test1 1 0] NOTICE: ['test1', 'test2'] NOTICE: DELETING: test1 1 0 NOTICE: PRECOMMIT NOTICE: COMMIT test2 | test1 -----------+----------- test2 2 0 | test1 1 0 (1 row) DROP foreign table testmulticorn_write; -- Now test with another column CREATE foreign table testmulticorn_write( test1 character varying, test2 character varying ) server multicorn_srv options ( option1 'option1', row_id_column 'test2' ); insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2'); NOTICE: [('option1', 'option1'), ('row_id_column', 'test2'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying'), ('test2', 'character varying')] NOTICE: INSERTING: [('test1', u'test'), ('test2', u'test2')] update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%'; NOTICE: [test1 ~~* test1 3%] NOTICE: ['test1', 'test2'] NOTICE: UPDATING: test2 1 1 with [('test1', u'test'), ('test2', u'test2 1 1')] NOTICE: UPDATING: test2 1 4 with [('test1', u'test'), ('test2', u'test2 1 4')] NOTICE: UPDATING: test2 1 7 with [('test1', u'test'), ('test2', u'test2 1 7')] NOTICE: UPDATING: test2 1 10 with [('test1', u'test'), ('test2', u'test2 1 10')] NOTICE: UPDATING: test2 1 13 with [('test1', u'test'), ('test2', u'test2 1 13')] NOTICE: UPDATING: test2 1 16 with [('test1', u'test'), ('test2', u'test2 1 16')] NOTICE: UPDATING: test2 1 19 with [('test1', u'test'), ('test2', u'test2 1 19')] delete from testmulticorn_write where test2 = 'test2 2 0'; NOTICE: [test2 = test2 2 0] NOTICE: ['test2'] NOTICE: DELETING: test2 2 0 update testmulticorn_write set test2 = 'test' where test2 = 'test2 1 1'; NOTICE: [test2 = test2 1 1] NOTICE: ['test1', 'test2'] NOTICE: UPDATING: test2 1 1 with [('test1', u'test1 3 1'), ('test2', u'test')] DROP foreign table testmulticorn_write; -- Now test with other types CREATE foreign table testmulticorn_write( test1 date, test2 timestamp ) server multicorn_srv options ( option1 'option1', row_id_column 'test2', test_type 'date' ); insert into testmulticorn_write(test1, test2) VALUES ('2012-01-01', '2012-01-01 00:00:00'); NOTICE: [('option1', 'option1'), ('row_id_column', 'test2'), ('test_type', 'date'), ('usermapping', 'test')] NOTICE: [('test1', 'date'), ('test2', 'timestamp without time zone')] NOTICE: INSERTING: [('test1', datetime.date(2012, 1, 1)), ('test2', datetime.datetime(2012, 1, 1, 0, 0))] delete from testmulticorn_write where test2 > '2011-12-03'; NOTICE: [test2 > 2011-12-03 00:00:00] NOTICE: ['test2'] NOTICE: DELETING: 2011-12-03 14:30:25 update testmulticorn_write set test1 = date_trunc('day', test1) where test2 = '2011-09-03 14:30:25'; NOTICE: [test2 = 2011-09-03 14:30:25] NOTICE: ['test1', 'test2'] NOTICE: UPDATING: 2011-09-03 14:30:25 with [('test1', datetime.date(2011, 9, 2)), ('test2', datetime.datetime(2011, 9, 3, 14, 30, 25))] DROP foreign table testmulticorn_write; -- Test with unknown column CREATE foreign table testmulticorn_write( test1 date, test2 timestamp ) server multicorn_srv options ( option1 'option1', row_id_column 'teststuff', test_type 'date' ); delete from testmulticorn_write; NOTICE: [('option1', 'option1'), ('row_id_column', 'teststuff'), ('test_type', 'date'), ('usermapping', 'test')] NOTICE: [('test1', 'date'), ('test2', 'timestamp without time zone')] ERROR: The rowid attribute does not exist DROP USER MAPPING for postgres SERVER multicorn_srv; DROP EXTENSION multicorn cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to server multicorn_srv drop cascades to foreign table testmulticorn drop cascades to foreign table testmulticorn_write