0

I want inserts made on a child table to cause a rule to be used that is defined on its parent table. Is this possible in PostgreSQL? Example follows:

create table master_ids(id bigint primary key);

CREATE TABLE ids (
    id bigint primary key references master_ids(id) deferrable initially deferred
);

CREATE TABLE stuff (
    id bigint primary key references master_ids(id) deferrable initially deferred
  ,data      text
) INHERITS (ids);

create or replace rule rule_id_insert as on insert to ids do also 
    insert into master_ids(id)
    select i.id
    from ids i
    where i.id not in (Select id from master_ids)
;

insert into stuff(id, data) values (1, 'kjkhkjh');

Gives error:

Error: ERROR: insert or update on table "stuff" violates foreign key constraint "stuff_id_fkey"
  Detail: Key (id)=(1) is not present in table "master_ids".
SQLState:  23503
ErrorCode: 0
Error occurred in:
insert into stuff(id, data) values (1, 'kjkhkjh')

If you are wondering why I want to do this, I want to create other tables like this:

CREATE TABLE other_stuff (
    id bigint primary key references master_ids(id) deferrable initially deferred
  ,data      text
) INHERITS (ids);

And sequences like this should fail:

insert into stuff(id, data) values (1, 'kjkhkjh');   

insert into other_stuff(id, data) values (1, 'kjkhkjh');

Without me having to make a rule for each child table.

Update: So I got a bit further by using a function like this:

create or replace function ufn_sync_ids() returns bigint as
$func$
with x as (
    insert into master_ids(id)
    select i.id
    from ids i
    where i.id not in (Select id from master_ids)
    )
select cast(0 as bigint)
$func$ LANGUAGE sql;

Which means that make the rule fire on child tables is a one liner:

create or replace rule rule_id_insert as on insert to stuff do also select ufn_sync_ids();

However, I realised that none of this actually prevents the id re-use across multiple child tables. What I am after is an inherited unique constraint, which postgres doesn't support... ¯_(ツ)_/¯

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.