4

Following table:

CREATE TABLE kontrolle.negativtext
(
  id serial NOT NULL,
  ausschluss character varying(255) NOT NULL, 
  nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  nicht_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  bemerkung character varying(255), 
  last_change_user character varying(3) NOT NULL,
  last_change_timestamp timestamp without time zone,
  hits integer NOT NULL DEFAULT 0,
  is_regex boolean NOT NULL DEFAULT false, 
  check_doc negative_doc_check[] DEFAULT '{CONTENT_TXT_DIRTY, CONTENT_TXT_CLEAN}', 
  CONSTRAINT negativtext_pkey PRIMARY KEY (id)
);

Values of datarecords in this table should be changeable by multiple users. I want to force everyone updating or inserting values in this table to provide a username (we don't need to discuss why this is no foreign key).
Setting the column to NOT NULL DEFAULT NULL will prevent the value to ever be null, which is fine. But when user abc inserted a row and user xyz updates it later, xyz should need to provide a value for column last_change_user, meaning this update statement should throw an error:

Update kontrolle.negativtext set ausschluss = :a, regex : rgx where id = :id

even if there is already a username set, but this should work:

Update kontrolle.negativtext
set ausschluss = :a, regex : rgx, last_change_user = :user
where id = :id

I think a constraint wouldn't work, a trigger before update on should work but I don't have success:

create trigger mandatory_username before update on kontrolle.negativtext
for each row
execute procedure check_mandatory_username();


create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    if NEW.last_change_user is null then 
        raise exception 'no last_change_user provided'
        USING HINT = 'please add your name';
    else return NEW;
    end if;
end;
$bla$ language plpgsql;

This never raises an exception. If I remove the condition-parts:

create or replace function check_mandatory_username()
returns trigger as $bla$
begin            
    raise exception 'no last_change_user provided'
    USING HINT = 'please add your name';            
end;
$bla$ language plpgsql;

then I get the message every time, so in general this could be the way to go. But I think NEW.last_change_user does not check the provided name, but only the new value (which is the old name).

Somebody have an idea?

  • which UPDATE query are you using to test the first trigger? can you confirm the column is set as: last_change_user character varying(3) NOT NULL DEFAULT NULL ? – AMG Jul 10 '18 at 16:01
  • @AMG I tried it with differend UPDATES, including the one (1st one) stated in the question, which should fail, but does not. No the column is exactly created as stated in the CREATE-Statement, so not NOT NULL DEFAULT NULL, only NOT NULL. – ctindex Jul 11 '18 at 14:33
  • ,The first query will never reach the IF. because you are not passing an explicit NULL value, since you are not updating that specific field. To trigger the error you must call the update like this: Update kontrolle.negativtext set ausschluss = :a, regex : rgx where id = :id, last_change_user = NULL. Any other try won't work. Good Luck! – AMG Jul 12 '18 at 14:05
  • Related answer on SO that may be of help: stackoverflow.com/questions/8759595/… – Erwin Brandstetter Jul 17 '18 at 13:04
3

Your check NEW.last_change_user is null in the trigger will only be true if the UPDATE statement explicitly contains a set last_change_user = NULL, not if the value did not change (then new and old are simply identical).

You can check if the value changed using if new.last_change_user is distinct from old.last_change_user, but you can not check if the value was specified at all, possibly supplying the same value that is already in the column.

Something like Oracle's updating(column_name) function would be needed here, but Postgres does not have that.

So I think the answer to your question is: no, that's not possible.

  • Okay thanks for the explanation, then I'll implement that part in the code (I simply prefer doing it database-wise if possible). – ctindex Jul 11 '18 at 14:37
0

Add a before update of last_change_user trigger that enforces that old.last_change_user <> new.last_change_user by adding a space or other noise charcter to the username.

Then add another trigger than runs for every update that checks that old.last_change_user <> new.last_change_user and the finally trims end spaces from new.last_change_user

you'll have to name the second trigger with an name that comes after the name of the first trigger.

If the user did not set last_change_user the first trigger won't run and then the second trigger will reject the update.

This means you won't be able to allow usernames that end with spaces, hopefully that won't be a problem :)

Your Answer

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

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