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
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 |