SET client_min_messages=NOTICE; CREATE EXTENSION multicorn; CREATE server multicorn_srv foreign data wrapper multicorn options ( wrapper 'multicorn.testfdw.TestForeignDataWrapper' ); CREATE user mapping FOR current_user server multicorn_srv options (usermapping 'test'); CREATE foreign table testmulticorn ( test1 character varying[], test2 character varying[] ) server multicorn_srv options ( option1 'option1', test_type 'list' ); -- Test "normal" usage select * from testmulticorn; NOTICE: [('option1', 'option1'), ('test_type', 'list'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying[]'), ('test2', 'character varying[]')] NOTICE: [] NOTICE: ['test1', 'test2'] test1 | test2 ------------------------------------------------------+------------------------------------------------------ {test1,1,0,"test1,\"0\"","{some value, \\\" ' 2}"} | {test2,2,0,"test2,\"0\"","{some value, \\\" ' 2}"} {test1,3,1,"test1,\"1\"","{some value, \\\" ' 2}"} | {test2,1,1,"test2,\"1\"","{some value, \\\" ' 2}"} {test1,2,2,"test1,\"2\"","{some value, \\\" ' 2}"} | {test2,3,2,"test2,\"2\"","{some value, \\\" ' 2}"} {test1,1,3,"test1,\"3\"","{some value, \\\" ' 2}"} | {test2,2,3,"test2,\"3\"","{some value, \\\" ' 2}"} {test1,3,4,"test1,\"4\"","{some value, \\\" ' 2}"} | {test2,1,4,"test2,\"4\"","{some value, \\\" ' 2}"} {test1,2,5,"test1,\"5\"","{some value, \\\" ' 2}"} | {test2,3,5,"test2,\"5\"","{some value, \\\" ' 2}"} {test1,1,6,"test1,\"6\"","{some value, \\\" ' 2}"} | {test2,2,6,"test2,\"6\"","{some value, \\\" ' 2}"} {test1,3,7,"test1,\"7\"","{some value, \\\" ' 2}"} | {test2,1,7,"test2,\"7\"","{some value, \\\" ' 2}"} {test1,2,8,"test1,\"8\"","{some value, \\\" ' 2}"} | {test2,3,8,"test2,\"8\"","{some value, \\\" ' 2}"} {test1,1,9,"test1,\"9\"","{some value, \\\" ' 2}"} | {test2,2,9,"test2,\"9\"","{some value, \\\" ' 2}"} {test1,3,10,"test1,\"10\"","{some value, \\\" ' 2}"} | {test2,1,10,"test2,\"10\"","{some value, \\\" ' 2}"} {test1,2,11,"test1,\"11\"","{some value, \\\" ' 2}"} | {test2,3,11,"test2,\"11\"","{some value, \\\" ' 2}"} {test1,1,12,"test1,\"12\"","{some value, \\\" ' 2}"} | {test2,2,12,"test2,\"12\"","{some value, \\\" ' 2}"} {test1,3,13,"test1,\"13\"","{some value, \\\" ' 2}"} | {test2,1,13,"test2,\"13\"","{some value, \\\" ' 2}"} {test1,2,14,"test1,\"14\"","{some value, \\\" ' 2}"} | {test2,3,14,"test2,\"14\"","{some value, \\\" ' 2}"} {test1,1,15,"test1,\"15\"","{some value, \\\" ' 2}"} | {test2,2,15,"test2,\"15\"","{some value, \\\" ' 2}"} {test1,3,16,"test1,\"16\"","{some value, \\\" ' 2}"} | {test2,1,16,"test2,\"16\"","{some value, \\\" ' 2}"} {test1,2,17,"test1,\"17\"","{some value, \\\" ' 2}"} | {test2,3,17,"test2,\"17\"","{some value, \\\" ' 2}"} {test1,1,18,"test1,\"18\"","{some value, \\\" ' 2}"} | {test2,2,18,"test2,\"18\"","{some value, \\\" ' 2}"} {test1,3,19,"test1,\"19\"","{some value, \\\" ' 2}"} | {test2,1,19,"test2,\"19\"","{some value, \\\" ' 2}"} (20 rows) select test1[2] as c from testmulticorn order by c; NOTICE: [] NOTICE: ['test1'] c --- 1 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 3 (20 rows) alter foreign table testmulticorn alter test1 type varchar; select * from testmulticorn; NOTICE: [('option1', 'option1'), ('test_type', 'list'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying'), ('test2', 'character varying[]')] NOTICE: [] NOTICE: ['test1', 'test2'] test1 | test2 ----------------------------------------------------------+------------------------------------------------------ ['test1', 1, 0, 'test1,"0"', '{some value, \\" \' 2}'] | {test2,2,0,"test2,\"0\"","{some value, \\\" ' 2}"} ['test1', 3, 1, 'test1,"1"', '{some value, \\" \' 2}'] | {test2,1,1,"test2,\"1\"","{some value, \\\" ' 2}"} ['test1', 2, 2, 'test1,"2"', '{some value, \\" \' 2}'] | {test2,3,2,"test2,\"2\"","{some value, \\\" ' 2}"} ['test1', 1, 3, 'test1,"3"', '{some value, \\" \' 2}'] | {test2,2,3,"test2,\"3\"","{some value, \\\" ' 2}"} ['test1', 3, 4, 'test1,"4"', '{some value, \\" \' 2}'] | {test2,1,4,"test2,\"4\"","{some value, \\\" ' 2}"} ['test1', 2, 5, 'test1,"5"', '{some value, \\" \' 2}'] | {test2,3,5,"test2,\"5\"","{some value, \\\" ' 2}"} ['test1', 1, 6, 'test1,"6"', '{some value, \\" \' 2}'] | {test2,2,6,"test2,\"6\"","{some value, \\\" ' 2}"} ['test1', 3, 7, 'test1,"7"', '{some value, \\" \' 2}'] | {test2,1,7,"test2,\"7\"","{some value, \\\" ' 2}"} ['test1', 2, 8, 'test1,"8"', '{some value, \\" \' 2}'] | {test2,3,8,"test2,\"8\"","{some value, \\\" ' 2}"} ['test1', 1, 9, 'test1,"9"', '{some value, \\" \' 2}'] | {test2,2,9,"test2,\"9\"","{some value, \\\" ' 2}"} ['test1', 3, 10, 'test1,"10"', '{some value, \\" \' 2}'] | {test2,1,10,"test2,\"10\"","{some value, \\\" ' 2}"} ['test1', 2, 11, 'test1,"11"', '{some value, \\" \' 2}'] | {test2,3,11,"test2,\"11\"","{some value, \\\" ' 2}"} ['test1', 1, 12, 'test1,"12"', '{some value, \\" \' 2}'] | {test2,2,12,"test2,\"12\"","{some value, \\\" ' 2}"} ['test1', 3, 13, 'test1,"13"', '{some value, \\" \' 2}'] | {test2,1,13,"test2,\"13\"","{some value, \\\" ' 2}"} ['test1', 2, 14, 'test1,"14"', '{some value, \\" \' 2}'] | {test2,3,14,"test2,\"14\"","{some value, \\\" ' 2}"} ['test1', 1, 15, 'test1,"15"', '{some value, \\" \' 2}'] | {test2,2,15,"test2,\"15\"","{some value, \\\" ' 2}"} ['test1', 3, 16, 'test1,"16"', '{some value, \\" \' 2}'] | {test2,1,16,"test2,\"16\"","{some value, \\\" ' 2}"} ['test1', 2, 17, 'test1,"17"', '{some value, \\" \' 2}'] | {test2,3,17,"test2,\"17\"","{some value, \\\" ' 2}"} ['test1', 1, 18, 'test1,"18"', '{some value, \\" \' 2}'] | {test2,2,18,"test2,\"18\"","{some value, \\\" ' 2}"} ['test1', 3, 19, 'test1,"19"', '{some value, \\" \' 2}'] | {test2,1,19,"test2,\"19\"","{some value, \\\" ' 2}"} (20 rows) alter foreign table testmulticorn options (set test_type 'nested_list'); select * from testmulticorn limit 1; NOTICE: [('option1', 'option1'), ('test_type', 'nested_list'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying'), ('test2', 'character varying[]')] NOTICE: [] NOTICE: ['test1', 'test2'] test1 | test2 --------------------------------------------------------------------+----------------------------------------------------------------------------- [['test1', 'test1'], [1, '{some value, \\" 2}'], [0, 'test1,"0"']] | {"['test2', 'test2']","[2, '{some value, \\\\\" 2}']","[0, 'test2,\"0\"']"} (1 row) alter foreign table testmulticorn alter test1 type varchar[]; alter foreign table testmulticorn alter test2 type varchar[][]; select test1[2], test2[2][2], array_length(test1, 1), array_length(test2, 1), array_length(test2, 2) from testmulticorn limit 1; NOTICE: [('option1', 'option1'), ('test_type', 'nested_list'), ('usermapping', 'test')] NOTICE: [('test1', 'character varying[]'), ('test2', 'character varying[]')] NOTICE: [] NOTICE: ['test1', 'test2'] test1 | test2 | array_length | array_length | array_length ----------------------------+--------------------+--------------+--------------+-------------- [1, '{some value, \\" 2}'] | {some value, \" 2} | 3 | 3 | 2 (1 row) select length(test1[2]) from testmulticorn limit 1; NOTICE: [] NOTICE: ['test1'] length -------- 26 (1 row) DROP USER MAPPING FOR current_user SERVER multicorn_srv; DROP EXTENSION multicorn cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to server multicorn_srv drop cascades to foreign table testmulticorn