CODEWITHSUNDEEP
javasqlhibernatepostgresql
kamil.rak
kamil.rak

Reputation: 1378

PostgreSQL getSequence current value and pass to id

I have a question reqarding function/triggers in postgreSql. I havae 2 following classes:

abstract public class person(){

int personid;
}

public class employee extends person(){

int employeeid;

}

I have also a sequence "hibernate_sequence"

So what I want to achieve is: by inserting into employee I'd like to get the current value of the hibernate_sentence and pass it to "employeid", so that both id's are equal.

So the trigger would be:

CREATE TRIGGER equalid before insert on employee EXECUTE PROCEDURE setid;

The function should do this:

CREATE FUNCTION setid() RETURNS numeric as '
update employee set employeeid = select currval(hibernate_sequence) where personid = select currval(hibernate_sequence)' language SQL;

But I have no idea how to achieve it. Postgresc doku is not clear to me at this point...

Upvotes: 0

Views: 71

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324335

You'll need to do this in Hibernate, not in PostgreSQL.

Hibernate fetches blocks of IDs and caches them. So PostgreSQL's view of hibernate_sequence's value is not what Hibernate is currently using.

Additionally, there's no way to say, after an insert into employee, whether a subsequent insert into person is for that employee or a different one, and there's no (supported, safe, reliable) place to stash a session variable to record the last employee ID inserted anyway.

You will want to do this in your hibernate logic.

Upvotes: 1

Related Questions