postgreSQL function select from list-CodePudding

I'm new to PostgreSQL and any other databases. I was trying to create a function (we are on version 9.6) that can select a temporary hashed password from a list and update the table and return the non hashed value, for example:

CREATE OR REPLACE FUNCTION updatePassword(_id varchar ) RETURNS varchar AS
$do$
BEGIN
        -- randomly select one
        -- passowrd1, hashedPasswordValue1
        -- password2, hashedPasswordValue2
        -- passowrd3, hashedPasswordValue3
        -- password4, hashedPasswordValue4

        UPDATE accounts 
        SET "password" = **hashedPasswordValue4**, last_password_change = NOW()
        WHERE id = _id ;
         
    
   RETURN 'The password has been updated successfully to **password4**';

END;
$do$ LANGUAGE plpgsql;

Or if it's no possible what alternatives are there to do this?

CodePudding user response：

You can declare an array in your function and then hash a random value from the array when you update the record

CREATE OR REPLACE FUNCTION updatePassword(_id int) RETURNS varchar AS
$do$
DECLARE
  list_password text[] := '{pass_1, pass_2, pass_3}'; 
  selected_password text = list_password[1   floor((random() * array_length(list_password, 1)))::int];
BEGIN   
   UPDATE accounts 
      SET "password" = md5(selected_password), last_password_change = NOW()
   WHERE id = _id ; 
   RETURN CONCAT('The password has been updated successfully to ', selected_password);
END;
$do$ LANGUAGE plpgsql;

select updatePassword(1)

Or you can generate a random value so as not to fill the array

CREATE OR REPLACE FUNCTION updatePassword(_id int) RETURNS varchar AS
$do$
DECLARE 
  random_password text = trunc(random() * 99999999);
BEGIN   
   UPDATE accounts 
      SET "password" = md5(random_password), last_password_change = NOW()
   WHERE id = _id ; 
   RETURN CONCAT('The password has been updated successfully to ', random_password);
END;
$do$ LANGUAGE plpgsql;

select updatePassword(1)

Demo in DBfiddle

CodePudding user response：

You can use a pair of CTE's to generate and then hash the password.
This isn't to recommend numeric passwords or met hashes, rather to demonstrate the CTE method.

create table accounts(id int, last_password_change date, password varchar(1000));

✓

insert into accounts (id) values
(1)

1 rows affected

with r as (
  select 
  substring(random()::varchar,3,4)
    as pword 
),
u as (
   UPDATE accounts 
   SET "password" = 
     (select md5(pword)
      from r), 
    last_password_change = NOW()
   WHERE id = 001
   returning id
)
SELECT 'id' " ", id::varchar from u
union all
select 'password', pword::varchar from r

         | id  
:------- | :---
id       | 1   
password | 1587

select * from accounts;

id | last_password_change | password                        
-: | :------------------- | :-------------------------------
 1 | 2022-04-29           | 49af6c4e558a7569d80eee2e035e2bd7

db<>fiddle here