Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE pwdhistory(
  usename text
, password text
, changed_on timestamptz DEFAULT now()
, UNIQUE (usename, password)
);

-- CREATE user foo;  -- ERROR:  permission denied to create role in fiddle

CREATE TABLE


CREATE OR REPLACE FUNCTION myfunc(_usename text, _password text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _min_password_length int := 8;  -- specify min length here
BEGIN
   IF length(_password) >= _min_password_length THEN
      -- not allowed in fiddle
      -- EXECUTE format('ALTER USER %I WITH PASSWORD %L', _usename, _password);
   ELSE  -- also catches NULL
      -- raise custom error
      RAISE EXCEPTION 'Password too short!'
      USING ERRCODE = '22023'  -- 22023 = "invalid_parameter_value'
          , DETAIL = 'Please check your password.'
          , HINT = 'Password must be at least ' || _min_password_length || ' characters.';
   END IF;

   
   INSERT INTO pwdhistory
          (usename, password, changed_on)
   VALUES ($1      , md5($2) , now());

EXCEPTION
   -- trap existing error and re-raise with added detail
   WHEN unique_violation THEN  -- = error code 23505
   
      RAISE unique_violation
      USING DETAIL = 'Password already used earlier. Please try again with a different password.';
END
$func$;

CREATE FUNCTION


SELECT myfunc('usr', 'pw12345');  -- too short

ERROR:  Password too short!
DETAIL:  Please check your password.
HINT:  Password must be at least 8 characters.
CONTEXT:  PL/pgSQL function myfunc(text,text) line 10 at RAISE


SELECT myfunc('usr', 'pw123456');  -- long enough


myfunc






SELECT 1


SELECT myfunc('usr', 'pw123456');  -- but don't repeat

ERROR:  unique_violation
DETAIL:  Password already used earlier. Please try again with a different password.
CONTEXT:  PL/pgSQL function myfunc(text,text) line 25 at RAISE