PostgreSQL 9.6 函数开发需求：实现随机选取哈希密码更新表并返回明文密码

Hey there! Since you're new to PostgreSQL (and databases in general), let's walk through exactly how to create this function, plus cover alternative approaches if you run into any roadblocks.

Core Implementation: Using a JSONB Password List #

For your PostgreSQL 9.6 setup, this approach uses a JSONB array to store your plaintext/hashed password pairs, then randomly selects one to update the accounts table. It's straightforward and keeps all your password options within the function itself.

CREATE OR REPLACE FUNCTION updatePassword(_id varchar) 
RETURNS varchar AS $do$
DECLARE
    -- Define your password pairs as a JSONB array
    password_list jsonb := '[
        {"plain": "password1", "hashed": "hashedPasswordValue1"},
        {"plain": "password2", "hashed": "hashedPasswordValue2"},
        {"plain": "password3", "hashed": "hashedPasswordValue3"},
        {"plain": "password4", "hashed": "hashedPasswordValue4"}
    ]'::jsonb;
    selected_entry jsonb;
    plain_password varchar;
    hashed_password varchar;
BEGIN
    -- Pick a random entry: calculate index using random() and array length
    selected_entry := password_list->floor(random() * jsonb_array_length(password_list))::int;
    
    -- Extract plaintext and hashed values from the selected entry
    plain_password := selected_entry->>'plain';
    hashed_password := selected_entry->>'hashed';
    
    -- Update the accounts table with the new hashed password and timestamp
    UPDATE accounts 
    SET "password" = hashed_password, 
        last_password_change = NOW() 
    WHERE id = _id;
    
    -- Return the plaintext password as requested
    RETURN 'The password has been updated successfully to ' || plain_password;
END;
$do$ LANGUAGE plpgsql;

Key Notes: #

• Make sure your accounts table has the correct column types: id as varchar, password sized to fit your hashed values, and last_password_change as timestamp or timestamptz.
• Hardcoding passwords in the function works for testing, but in production, you’ll want to avoid this (more on that in alternatives below).

Alternative Approaches #

If for some reason the JSONB method doesn’t fit your needs, here are two solid alternatives:

1. Use a Dedicated Password Lookup Table #

This is better if you need to update your password list frequently without editing the function, and it’s more secure (you can restrict access to the lookup table).

First, create and populate the lookup table:

CREATE TABLE IF NOT EXISTS password_options (
    plaintext varchar NOT NULL,
    hashed_value varchar NOT NULL
);

INSERT INTO password_options VALUES
('password1', 'hashedPasswordValue1'),
('password2', 'hashedPasswordValue2'),
('password3', 'hashedPasswordValue3'),
('password4', 'hashedPasswordValue4');

Then the function:

CREATE OR REPLACE FUNCTION updatePassword(_id varchar) 
RETURNS varchar AS $do$
DECLARE
    plain_password varchar;
    hashed_password varchar;
BEGIN
    -- Randomly select one password pair from the lookup table
    SELECT plaintext, hashed_value
    INTO plain_password, hashed_password
    FROM password_options
    ORDER BY random()
    LIMIT 1;
    
    -- Update the accounts table
    UPDATE accounts 
    SET "password" = hashed_password, 
        last_password_change = NOW() 
    WHERE id = _id;
    
    RETURN 'The password has been updated successfully to ' || plain_password;
END;
$do$ LANGUAGE plpgsql;

2. Use a 2D Array #

If you prefer to avoid JSONB and lookup tables, a 2D array works just as well in PostgreSQL 9.6:

CREATE OR REPLACE FUNCTION updatePassword(_id varchar) 
RETURNS varchar AS $do$
DECLARE
    -- Store password pairs in a 2D array (plaintext at index [n][1], hashed at [n][2])
    password_list varchar[][] := ARRAY[
        ['password1', 'hashedPasswordValue1'],
        ['password2', 'hashedPasswordValue2'],
        ['password3', 'hashedPasswordValue3'],
        ['password4', 'hashedPasswordValue4']
    ];
    selected_index int;
    plain_password varchar;
    hashed_password varchar;
BEGIN
    -- Generate a random index (PostgreSQL arrays start at 1, so we add 1 to the floor value)
    selected_index := floor(random() * array_length(password_list, 1)) + 1;
    
    plain_password := password_list[selected_index][1];
    hashed_password := password_list[selected_index][2];
    
    UPDATE accounts 
    SET "password" = hashed_password, 
        last_password_change = NOW() 
    WHERE id = _id;
    
    RETURN 'The password has been updated successfully to ' || plain_password;
END;
$do$ LANGUAGE plpgsql;

All these methods work perfectly with PostgreSQL 9.6, so pick the one that fits your workflow best!

内容的提问来源于stack exchange，提问作者James May