20110818_fdw_option_format.patch
text/plain
Filename: 20110818_fdw_option_format.patch
Type: text/plain
Part: 0
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 39c7136..8518215 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*************** describeOneTableDetails(const char *sche
*** 1272,1278 ****
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n a.attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
--- 1272,1280 ----
else
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
! appendPQExpBuffer(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM "
! " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
else
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
if (verbose)
*************** describeOneTableDetails(const char *sche
*** 2038,2044 ****
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " f.ftoptions\n"
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
--- 2040,2049 ----
/* Footer information about foreign table */
printfPQExpBuffer(&buf,
"SELECT s.srvname,\n"
! " array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') "
"FROM pg_catalog.pg_foreign_table f,\n"
" pg_catalog.pg_foreign_server s\n"
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
*************** describeOneTableDetails(const char *sche
*** 2061,2067 ****
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: %s", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
--- 2066,2072 ----
ftoptions = PQgetvalue(result, 0, 1);
if (ftoptions && ftoptions[0] != '\0')
{
! printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
printTableAddFooter(&cont, buf.data);
}
PQclear(result);
*************** listForeignDataWrappers(const char *patt
*** 3679,3685 ****
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n fdwoptions AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
--- 3684,3695 ----
appendPQExpBuffer(&buf, ",\n ");
printACLColumn(&buf, "fdwacl");
appendPQExpBuffer(&buf,
! ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(fdwoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
if (pset.sversion >= 90100)
*************** listForeignServers(const char *pattern,
*** 3752,3758 ****
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " s.srvoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
--- 3762,3773 ----
",\n"
" s.srvtype AS \"%s\",\n"
" s.srvversion AS \"%s\",\n"
! " CASE WHEN srvoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(srvoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("Type"),
gettext_noop("Version"),
*************** listUserMappings(const char *pattern, bo
*** 3818,3824 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n um.umoptions AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
--- 3833,3844 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(umoptions)), ', ') || ')' "
! " END AS \"%s\"",
gettext_noop("FDW Options"));
appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
*************** listForeignTables(const char *pattern, b
*** 3873,3879 ****
if (verbose)
appendPQExpBuffer(&buf,
! ",\n ft.ftoptions AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
--- 3893,3904 ----
if (verbose)
appendPQExpBuffer(&buf,
! ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
! " '(' || array_to_string(ARRAY(SELECT "
! " option_name || ' ' || "
! " quote_literal(option_value) FROM "
! " pg_options_to_table(ftoptions)), ', ') || ')' "
! " END AS \"%s\",\n"
" d.description AS \"%s\"",
gettext_noop("FDW Options"),
gettext_noop("Description"));
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 4b60e8c..8bae4d9 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** ERROR: foreign-data wrapper "foo" alrea
*** 52,63 ****
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 52,63 ----
DROP FOREIGN DATA WRAPPER foo;
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** CREATE FOREIGN DATA WRAPPER foo OPTIONS
*** 65,76 ****
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {testing=1,another=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
--- 65,76 ----
ERROR: option "testing" provided more than once
CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (testing '1', another '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP FOREIGN DATA WRAPPER foo;
*************** ALTER FOREIGN DATA WRAPPER foo OPTIONS (
*** 113,146 ****
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {a=1,b=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+---------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
--- 113,146 ----
ERROR: option "c" not found
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (a '1', b '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+----------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-----------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
*************** HINT: Must be superuser to alter a fore
*** 150,161 ****
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
--- 150,161 ----
SET ROLE regress_test_role_super;
ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
*************** ERROR: permission denied to alter forei
*** 169,180 ****
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
--- 169,180 ----
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- DROP FOREIGN DATA WRAPPER
*************** ERROR: foreign-data wrapper "nonexisten
*** 183,194 ****
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+-------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
--- 183,194 ----
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
\dew+
! List of foreign-data wrappers
! Name | Owner | Handler | Validator | Access privileges | FDW Options | Description
! ------------+-------------------------+---------+--------------------------+-------------------+------------------------------+-------------
! dummy | foreign_data_user | - | - | | | useless
! foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
! postgresql | foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
*************** ERROR: invalid option "foo"
*** 289,305 ****
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
(8 rows)
SET ROLE regress_test_role;
--- 289,305 ----
HINT: Valid options in this context are: authtype, service, connect_timeout, dbname, host, hostaddr, port, tty, options, requiressl, sslmode, gsslib
CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
(8 rows)
SET ROLE regress_test_role;
*************** SET ROLE regress_test_role;
*** 311,328 ****
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
--- 311,328 ----
CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
RESET ROLE;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
(9 rows)
REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
*************** GRANT regress_test_indirect TO regress_t
*** 335,353 ****
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b} |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
--- 335,353 ----
SET ROLE regress_test_role;
CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | | | | |
! s2 | foreign_data_user | foo | | | | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
RESET ROLE;
*************** ALTER SERVER s3 OPTIONS (tnsname 'orcl',
*** 365,385 ****
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
--- 365,385 ----
GRANT USAGE ON FOREIGN SERVER s1 TO regress_test_role;
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-------------------+----------------------+-----------------------------------------+--------+---------+-----------------------------------+-------------
! s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 1.0 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | (tnsname 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
SET ROLE regress_test_role;
*************** ERROR: role "regress_test_indirect" can
*** 416,436 ****
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | {servername=s1} |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b} |
! s3 | foreign_data_user | foo | | oracle | | {tnsname=orcl,port=1521} |
! s4 | foreign_data_user | foo | | oracle | | {host=a,dbname=b} |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | {host=a,dbname=b} |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b} |
! s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30} |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
--- 416,436 ----
DETAIL: owner of server s1
privileges for foreign-data wrapper foo
\des+
! List of foreign servers
! Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
! ------+-----------------------+----------------------+-----------------------------------------+--------+---------+--------------------------------------+-------------
! s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user +| | 1.1 | (servername 's1') |
! | | | regress_test_role=U/foreign_data_user | | | |
! s2 | foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
! s3 | foreign_data_user | foo | | oracle | | (tnsname 'orcl', port '1521') |
! s4 | foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
! s5 | foreign_data_user | foo | | | 15.0 | |
! s6 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user +| | 16.0 | (host 'a', dbname 'b') |
! | | | regress_test_role2=U*/foreign_data_user | | | |
! s7 | foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
! s8 | foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
! t1 | regress_test_role | foo | | | | |
! t2 | regress_test_role | foo | | | | |
(10 rows)
-- DROP SERVER
*************** ERROR: must be owner of foreign server
*** 584,599 ****
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+-----------------------------
s4 | foreign_data_user |
! s4 | public | {"mapping=is public"}
! s5 | regress_test_role | {modified=1}
! s6 | regress_test_role | {username=test}
! s8 | foreign_data_user | {password=public}
! t1 | public | {modified=1}
! t1 | regress_test_role | {username=bob,password=boo}
(7 rows)
-- DROP USER MAPPING
--- 584,599 ----
ALTER USER MAPPING FOR public SERVER t1 OPTIONS (ADD modified '1');
RESET ROLE;
\deu+
! List of user mappings
! Server | User name | FDW Options
! --------+-------------------+----------------------------------
s4 | foreign_data_user |
! s4 | public | (mapping 'is public')
! s5 | regress_test_role | (modified '1')
! s6 | regress_test_role | (username 'test')
! s8 | foreign_data_user | (password 'public')
! t1 | public | (modified '1')
! t1 | regress_test_role | (username 'bob', password 'boo')
(7 rows)
-- DROP USER MAPPING
*************** CREATE FOREIGN TABLE ft1 (
*** 653,673 ****
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain | ft1.c1
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
\det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-------+--------+----------------------------+-------------
! public | ft1 | sc | {"delimiter=,","quote=\""} | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
--- 653,673 ----
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | (param1 'val1') | plain | ft1.c1
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"')
Has OIDs: no
\det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-------+--------+----------------------------+-------------
! public | ft1 | sc | (delimiter ',', quote '"') | ft1
(1 row)
CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
*************** ALTER FOREIGN TABLE ft1 ALTER COLUMN c7
*** 705,724 ****
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+---------------------------+----------+-------------
! c1 | integer | not null | {param1=val1} | plain |
! c2 | text | | {param2=val2,param3=val3} | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | {p1=v1,p2=v2} | plain |
! c8 | text | | {p2=V2} | extended |
! c9 | integer | | | plain |
! c10 | integer | | {p1=v1} | plain |
Server: sc
! FDW Options: {"delimiter=,","quote=\""}
Has OIDs: no
-- can't change the column type if it's used elsewhere
--- 705,724 ----
ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
\d+ ft1
! Foreign table "public.ft1"
! Column | Type | Modifiers | FDW Options | Storage | Description
! --------+---------+-----------+--------------------------------+----------+-------------
! c1 | integer | not null | (param1 'val1') | plain |
! c2 | text | | (param2 'val2', param3 'val3') | extended |
! c3 | date | | | plain |
! c4 | integer | | | plain |
! c6 | integer | not null | | plain |
! c7 | integer | | (p1 'v1', p2 'v2') | plain |
! c8 | text | | (p2 'V2') | extended |
! c9 | integer | | | plain |
! c10 | integer | | (p1 'v1') | plain |
Server: sc
! FDW Options: (delimiter ',', quote '"')
Has OIDs: no
-- can't change the column type if it's used elsewhere
*************** ERROR: relation "ft1" does not exist
*** 749,767 ****
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+---------------------------
! foreign_column_1 | integer | not null | {param1=val1}
! c2 | text | | {param2=val2,param3=val3}
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | {p1=v1,p2=v2}
! c8 | text | | {p2=V2}
! c10 | integer | | {p1=v1}
Server: sc
! FDW Options: {quote=~,escape=@}
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
--- 749,767 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
! Foreign table "foreign_schema.foreign_table_1"
! Column | Type | Modifiers | FDW Options
! ------------------+---------+-----------+--------------------------------
! foreign_column_1 | integer | not null | (param1 'val1')
! c2 | text | | (param2 'val2', param3 'val3')
c3 | date | |
c4 | integer | |
c6 | integer | not null |
! c7 | integer | | (p1 'v1', p2 'v2')
! c8 | text | | (p2 'V2')
! c10 | integer | | (p1 'v1')
Server: sc
! FDW Options: (quote '~', escape '@')
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;