Postgres 12

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 or replace function generate_random_token()
returns text as
$$
begin
	return md5(random()::text);
end;
$$ language plpgsql;
create or replace function generate_password_hash(plain text)
returns text as
$$
begin
	return plain;
end;
$$ language plpgsql;
create or replace function validate_password_hash(plain text, hash text)
returns text as
$$
begin
	return plain;
end;
$$ language plpgsql;
create type token_response AS (access_token text, refresh_token text);
create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
begin
    select id
    into v_user_id
    from users
    where username = p_user
    	and password = validate_password_hash(p_pass, password)
    limit 1;

    if (v_user_id is not null) then
        select id, updated_on, access_token, refresh_token
        into v_token_id, v_updated_on, v_access_token, v_refresh_token
        from tokens
        where user_id = v_user_id
        order by updated_on desc
        limit 1;

        if (v_token_id is null) then
            insert into tokens (user_id)
            values (v_user_id)
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;

        if (v_updated_on < current_timestamp - interval '1 day') then
            update tokens
            set access_token = generate_random_token(),
                refresh_token = generate_random_token(),
                updated_on = current_timestamp
            where id = v_token_id
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;
    end if;

    return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;
create table users (
  id int generated always as identity primary key,
  username text not null unique,
  password text not null
);
create table tokens (
  id int generated always as identity primary key,
  user_id int not null references users(id),
  access_token text unique default generate_random_token(),
  refresh_token text default generate_random_token(),
  updated_on timestamp with time zone default current_timestamp,
  created_on timestamp with time zone default current_timestamp
);
insert into
  users(username, password)
  values('foo', generate_password_hash('bar'));
1 rows affected

select * from users;


id
username
password


1
foo
bar


select * from user_auth('foo', 'bar');


access_token
refresh_token


f15966df2b7ddd5318026e7b2ada544c
045ddecfc0d54a1829b82db886d2431d