pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: peter.borissow@kartographia.com <peter.borissow@kartographia.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Inserting into foreign table with sequences and default values
Date: Fri, 8 Dec 2023 18:44:23 -0500 (EST)
Message-ID: <1702079063.614617754@apps.rackspace.com> (raw)
In-Reply-To: <1468017.1702060306@sss.pgh.pa.us>
References: <1702058638.91064408@apps.rackspace.com>
	<1468017.1702060306@sss.pgh.pa.us>


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');


 
 
 

view thread (4+ messages)  latest in thread

Message-ID: <1702079063.614617754@apps.rackspace.com>
Permalink:  ../1702079063.614617754@apps.rackspace.com/
Also on:    postgresql.org/message-id/1702079063.614617754@apps.rackspace.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: peter.borissow@kartographia.com, tgl@sss.pgh.pa.us
  Subject: Re: Inserting into foreign table with sequences and default values
  In-Reply-To: <1702079063.614617754@apps.rackspace.com>
  References: <1702058638.91064408@apps.rackspace.com> <1468017.1702060306@sss.pgh.pa.us> <1702079063.614617754@apps.rackspace.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox