Sequences in foreign tables

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Sequences in foreign tables
Date: 2014-09-16 15:05:47
Message-ID: CA+mi_8bfkaFPNPPx6_W_T_0J9OEMSfXQKCDZo=OMJpWWcCKtoA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm learning now something about foreign tables in PG 9.3. I wonder if
there is a clean way to use a sequence on the remote side, so that an
"insert into remote_table values ([data not including id]) returning
id" would ask the remote server to generate a new value for id.

As it stands now defaults are evaluated client-side and a column with
no default is treated as defaulting to null (as per docs): I see from
the logs that explicit nulls are sent to the server even if not
requested by the insert, or if "default" is specified.

I've worked around that using a trigger on the remote table to
reinstate the defaults, something along the line of:

create or replace function ... returning trigger
begin
if new.id is null then new.id = nextval('seqname'::regclass); end if;
if new.cr_date is null then new.cr_date = now(); end if;
return new;
end

create trigger ... before insert for each row...

but I wonder if there is a more idiomatic way to do that. Using a
sequence on the remote side instead of the local seems a basic use
case and local sequences are not an option if more than one database
have a foreign table on the same physical table.

Thank you very much

-- Daniele

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2014-09-16 15:39:02 Re: pg_dump does not include database-level user-defined GUC variables?
Previous Message Adrian Klaver 2014-09-16 14:21:00 Re: FATAL: terminating connection due to administrator command