POSTGRESQL - Insert into foreign table triggered insertion into another remote table

Question

I have a problem to update records into my foreign table.

It refers to a remote table which have a trigger to insert into another remote table (account_journals table).

I recreated same trigger and adding another foreign table which refers to the account_journals table, but i still have same error.

How i created my foreigns tables :

CREATE FOREIGN TABLE accounts (
...
)
SERVER fdw_server
OPTIONS (schema_name 'public', table_name 'remote_accounts');
CREATE FOREIGN TABLE

CREATE FOREIGN TABLE account_journals (
...
)
SERVER fdw_server
OPTIONS (schema_name 'public', table_name 'remote_account_journals');
CREATE FOREIGN TABLE

The trigger :

CREATE TRIGGER tg_account_journals AFTER INSERT OR DELETE OR UPDATE ON accounts FOR EACH row EXECUTE FUNCTION fill_account_journals();

fill_account_journals() function just insert record into account_journals.

When update record into accounts table :

new_database=# UPDATE accounts SET name = 'valou' WHERE id = 1;                                                                    
ERROR:  relation "remote_account_journals" does not exist
CONTEXT:  PL/pgSQL function public.fill_remote_account_journals() line 8 at SQL statement
remote SQL command: UPDATE public.remote_accounts SET name = $2 WHERE ctid = $1 RETURNING id, name, ...

Any help is appreciated to solve my problem, thanks

1) You need to add the trigger function as update to question. 2) What happens if you do a stand alone `select * from account_journals` query? — Adrian Klaver, Jan 24 '23 at 15:29
I don't understand the end of your first question ? For the second, i have all my finded records in remote table — Valentin Leguy, Jan 24 '23 at 15:40
You need to add the content of `public.fill_remote_account_journals()` to your question. — Adrian Klaver, Jan 24 '23 at 15:42

POSTGRESQL - Insert into foreign table triggered insertion into another remote table

0 Answers0