Manage revisions in postgresql

Question

I am currently trying to manage revisions of a data set in a postgreSql database. The table I would like to use has the following structure:

CREATE TABLE dataset (
   id BIGSERIAL PRIMARY KEY,
   revision INTEGER NOT NULL,
   object_id BIGINT NOT NULL
);

The id field is a unique auto-increment identifier. The object_id should be the identifier for a object, while revision keeps track of the revisions:

id | object_id | revision
-------------------------
 1 |         1 |        1
 2 |         2 |        1
 3 |         1 |        2
 4 |         1 |        3
 5 |         3 |        1
 6 |         4 |        1 

What I now need is a function, that:

1. Sets a auto-increment object_id and sets revision to 1, if no object_id is provided.
2. Sets a auto-increment revision for this object_id, if an object_id is provided.

I already found this answer, but this does not really solve the problem of creating consecutive revisions for a object_id and it does not solve the problem of auto creating consecutive object_ids.

EDIT:

I would do something like the following, but this doesn't feel very comfortable:

CREATE OR REPLACE FUNCTION update_revision() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
    IF tg_op='INSERT' THEN
        IF NEW.object_id != NULL THEN
            NEW.object_id = SELECT nextval(object_id_seq_id);
            NEW.revision = 1;
        ELSE
            NEW.revision = SELECT MAX(revision)+1 FROM dataset WHERE spot_id = NEW.spot_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER update_revision BEFORE INSERT OR UPDATE ON dataset
    FOR EACH ROW EXECUTE PROCEDURE update_revision();

Answer 1

Make (object_id, revision) unique. BTW why aren't they the primary key?

create table dataset (
    id bigserial primary key,
    object_id bigint not null,
    revision integer not null,
    unique (object_id, revision)
);

create or replace function include_revision (_object_id integer)
returns dataset as $$
    with object_id as (
        select coalesce(max(object_id), 0) + 1 as object_id
        from dataset
    ), revision as (
        select coalesce(max(revision), 0) + 1 as revision
        from dataset
        where object_id = _object_id
    )
    insert into dataset (object_id, revision)
    select
        coalesce(_object_id, (select object_id from object_id)),
        (select revision from revision)
    returning *
    ;
$$ language sql;

object_id is set to coalesce(_object_id, (select object_id from object_id)), that is, only if _object_id is null it will use the calculated max(object_id)

Testing:

select include_revision(null);
 include_revision 
------------------
 (1,1,1)

select include_revision(1);
 include_revision 
------------------
 (2,1,2)

select include_revision(null);
 include_revision 
------------------
 (3,2,1)