Thread: Options for Postgres FDW
I´m trying to insert/update some tables using FDW. I´m having problems when I want to use default values and triggers. For columns with default values, if I drop that column on foreign table definition is fine for inserts but I cannot use that column for updates/selects because it doesn´t exist on foreign table definition. For triggers, there is a way to define search_path for that server/connection ? Because suppose I´ve used "import foreign schema CustomerSchema", when I do some insert/update on any table of this schema, if that table calls a trigger which needs another table, that trigger doesn´t sees that table because it´s using pg_catalog schema. The only way to solve this is defining search_path when starting all triggers ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Additionally, there is a list of all options for create foreign server and create foreign table ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 1/30/20 2:01 PM, PegoraroF10 wrote: > Additionally, there is a list of all options for create foreign server and > create foreign table ? That is going to depend on the FDW. Options that make sense for connecting to a database make not make sense for a file based FDW. For postgres-fdw see: https://www.postgresql.org/docs/12/postgres-fdw.html#id-1.11.7.42.10 > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
On 1/30/20 1:59 PM, PegoraroF10 wrote: > I´m trying to insert/update some tables using FDW. I´m having problems when I > want to use default values and triggers. > > For columns with default values, if I drop that column on foreign table > definition is fine for inserts but I cannot use that column for > updates/selects because it doesn´t exist on foreign table definition. Don't drop it? > > For triggers, there is a way to define search_path for that > server/connection ? Because suppose I´ve used "import foreign schema ?: https://www.postgresql.org/docs/12/sql-alterrole.html ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } > CustomerSchema", when I do some insert/update on any table of this schema, > if that table calls a trigger which needs another table, that trigger > doesn´t sees that table because it´s using pg_catalog schema. The only way > to solve this is defining search_path when starting all triggers ? > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com