Using trigger for sorting in PostgreSQL

Question

I am working on a Spring-MVC application where I am using PostgreSQL database with hibernate. I realize that Hibernate has its own trigger but using them involves a lot of overhead. For this reason, I would like to use a PostgreSQL trigger.

Have a look at the data-model pasted below.

Question :What should it do? Answer : After database gets updated(CRUD operations), there is a integer-column(noteorder) which defines ordering of the data. I would like to reset that and start it to 1 and increment until end of row.

There is a catch too: :-( Can I pass parameters like do use triggers when certain conditions like sectionid or canvasid is fulfilled?

Is this possible or do I have to Hibernate triggers. Kindly let me know, any ideas, link would be nice. If any question, please leave a comment. Thank you.

Below is the data-model :

CREATE TABLE note
(
  noteid integer NOT NULL,
  sectionid integer,
  canvasid integer,
  text character varying,
  notecolor character varying,
  noteheadline character varying,
  id integer NOT NULL,
  canvasname character varying,
  noteorder integer,
  CONSTRAINT noteid PRIMARY KEY (noteid),
  CONSTRAINT user_note_fk FOREIGN KEY (id)
      REFERENCES person (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Bruno Calza · Accepted Answer · 2014-11-03 17:29:33Z

1

You can run an update query using the row_number window function

UPDATE note set note.noteorder = subquery.rn
FROM  
(
    SELECT id,row_number() over () AS rn
    FROM note
) subquery
WHERE note.id = subquery.id;

You can user a trigger but you cannot pass any arguments. From docs:

The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

edited Nov 3, 2014 at 17:29

answered Nov 3, 2014 at 16:38

Bruno Calza

2,8002 gold badges25 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

We are Borg Over a year ago

Thanks. I guess then I cannot use it, as I need to pass 2 parameters under any conditions. Thank you for your answer, I guess I will look into hibernate for some solutions.

user330315 Over a year ago

And you probably want a order by noteorder in the over clause to preserve the current sort order. Otherwise this will just be a "random" order.

Collectives™ on Stack Overflow

Using trigger for sorting in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related