Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.

share|improve this question
1  
That's not possible with a constraint. A (statement level) trigger might be able to achieve something like that. –  a_horse_with_no_name Nov 11 '13 at 21:43
    
Can you elaborate why you need this? –  A-K Nov 12 '13 at 3:37

1 Answer 1

up vote 3 down vote accepted

One way to implement this would be with a trigger updating a temporary table in parallel (that is only visible inside the same session).

Create the temp table with ON COMMIT DROP, so that it lives for the duration of a transaction. This way, all updates during the same transaction count against the maximum.

Demo table:

CREATE TABLE foo(id serial, txt text)

Trigger function:

CREATE OR REPLACE FUNCTION trg_foo_maxup()
  RETURNS trigger AS
$func$
DECLARE
  _ct int;
BEGIN

IF EXISTS (
   SELECT 1 FROM pg_catalog.pg_class
   WHERE  relname = 'foo_maxup'
   AND    relnamespace = pg_my_temp_schema()
   AND    relkind = 'r'
   ) THEN

   UPDATE foo_maxup SET ct = ct + 1
   RETURNING ct INTO _ct;

   IF _ct > 5 THEN           -- your maximum here
      RAISE EXCEPTION 'Cannot update more than 5 rows in one transaction!';
   END IF;

ELSE
   CREATE TEMP TABLE foo_maxup ON COMMIT DROP AS
   SELECT 1 AS ct;
END IF;

RETURN NEW;

END
$func$
  LANGUAGE plpgsql VOLATILE SET search_path = pg_temp;

Trigger:

CREATE TRIGGER maxup
BEFORE UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo_maxup();

If you want a maximum for the whole session, just omit ON COMMIT DROP.

I added SET search_path = pg_temp to the function definition to prevent mischief with the search_path. This trigger only sees temporary tables in its search_path. That's why pg_catalog.pg_class has to be table-qualified.

-> SQLfiddle demo.

share|improve this answer
    
That is a pretty nifty idea with the temp table. –  a_horse_with_no_name Nov 12 '13 at 16:54
    
@a_horse_with_no_name: My first impulse was a statement level trigger, too. But after some consideration I found this to be safe and cheap for a small maximum. –  Erwin Brandstetter Nov 12 '13 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.