Sequences in foreign tables
| От | Daniele Varrazzo |
|---|---|
| Тема | Sequences in foreign tables |
| Дата | |
| Msg-id | CA+mi_8bfkaFPNPPx6_W_T_0J9OEMSfXQKCDZo=OMJpWWcCKtoA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Sequences in foreign tables
|
| Список | 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
В списке pgsql-general по дате отправления: