From: | "peter(dot)borissow(at)kartographia(dot)com" <peter(dot)borissow(at)kartographia(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inserting into foreign table with sequences and default values |
Date: | 2023-12-08 23:44:23 |
Message-ID: | 1702079063.614617754@apps.rackspace.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Tom,
Your reply helped point me in the right direction. With a little trial and error I came up with a hack to solve my issue.
First off, I create a shared sequence for the user_id with a technique described here:
[ https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ ]( https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ )
This involved creating a sequence on the remote server and a view of the sequence on the remote server. I also updated the "test" table on the remote server to populate the "id" column using a the sequence via a trigger.
On the local server, I created a foreign table that references the view on the remote server and a function that returns a sequence value from the foreign table.
Finally, on the local server I created a trigger on the remote "test" table that was imported earlier via the "IMPORT FOREIGN SCHEMA" command.
It's all a little hacky but it works :-)
Full steps below for anyone that's interested (sorry if it gets mangled via email).
Thanks Again,
Peter
--------------------
On Remote:
--------------------
CREATE SEQUENCE user_id;
CREATE VIEW user_id_view AS SELECT nextval('user_id') as a;
CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_view;' LANGUAGE SQL;
CREATE TABLE APP.TEST (
ID bigint NOT NULL,
FIRST_NAME text,
LAST_NAME text,
STATUS integer NOT NULL DEFAULT 1,
CONSTRAINT PK_USER PRIMARY KEY (ID)
);
CREATE OR REPLACE FUNCTION test_function()
RETURNS "trigger" AS
$BODY$
BEGIN
New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
New.status := case when New.status IS NULL then 1 else New.status end;
Return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER test_trigger
BEFORE INSERT
ON app.test
FOR EACH ROW
EXECUTE PROCEDURE test_function();
--------------------
On Local
--------------------
CREATE FOREIGN TABLE user_id_foreign_table (a bigint)
SERVER culmen OPTIONS (table_name 'user_id_view');
CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_foreign_table;' LANGUAGE SQL;
IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
FROM SERVER culmen INTO public;
CREATE OR REPLACE FUNCTION test_function()
RETURNS "trigger" AS
$BODY$
BEGIN
New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
New.status := case when New.status IS NULL then 1 else New.status end;
Return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER test_trigger
BEFORE INSERT
ON test
FOR EACH ROW
EXECUTE PROCEDURE test_function();
INSERT INTO test(first_name) VALUES ('Bob');
From | Date | Subject | |
---|---|---|---|
Next Message | peter.borissow@kartographia.com | 2023-12-09 01:17:59 | Re: Inserting into foreign table with sequences and default values |
Previous Message | Peter J. Holzer | 2023-12-08 22:31:50 | Re: Store PDF files in PostgreDB |