This is just for fun.
Let's create an auth
table. It will hold a username primary key and a password.
create table auth (
username text primary key,
password text not null
);
To create a user, we need a function that uses pgcrypto
to generate a password with salt. Enable that with create extension pgcrypto;
.
create function auth_insert(
p_username text,
p_password text)
returns text
language plpgsql
as $$
declare
p_password_crypt text :=
crypt(p_password, gen_salt('bf'));
begin
insert into auth (username, password)
values (p_username, p_password_crypt);
return p_username;
end;
$$;
We need a session
table to store session tokens.
create table session (
uuid text primary key,
username text references auth(username) not null
);
We need a login function that checks the supplied password and add session entry using gen_random_uuid()
.
create function auth_login(
p_username text,
p_password text)
returns text
language plpgsql
as $$
declare
found_username text;
gen_uuid text := gen_random_uuid();
begin
select auth.username into found_username
from auth where auth.username = p_username
and password = crypt(p_password, password);
if not found then
raise exception 'user not found'; end if;
insert into session (uuid, username)
values (gen_uuid, found_username);
return gen_uuid;
end;
$$;
We need a function to check if the user's session token is valid.
create function auth_session(
p_session text)
returns text
language plpgsql
as $$
declare
p_username text;
begin
select username into p_username from session
where session.uuid = p_session;
if p_username is null then
raise exception 'user not found'; end if;
return p_username;
end
$$;
Finally we can register a user, login and check the session token:
select * from auth_insert('auser', 'apassword');
select * from auth_login('auser', 'apassword');
-- copy the returned session token and run
select * from auth_session('...');
-- now you should see the username for that sessoin
There are obvious caveats: the session table grows infinitely; the username can't easily be changed when we start using it as a foreign key; if we try to register with multiple username sql throws a unique constraint exception; in fact, there is next to no fine tuned exception handling; generating the salted passwords in a reproducible database may be a security vulnerability; using the text
type means there's no limit to input; and more.
But let's continue to add this CRUD functions in the next post.
We use json_array_elements_text
, or its jsonb equivalent, to get the elements as a text table.
And put that into a select array()
statement.
select array(
select jsonb_array_elements_text('["hi", "there"]')
);
Using postgresql's with
expressions, we:
unnest
the array into a tablearray
to pack that up into an array again.with
unnested as (select unnest(arr) as str),
sorted_w_len as (select str, length(str) as str_len
from unnested order by str_len desc),
sorted as (select str from sorted_w_len)
select array(select * from sorted);
Putting that into a function is a good idea:
create or replace function _array_str_length_sort(arr text[])
returns text[]
language plpgsql
as $$
declare
ret text[];
begin
with
unnested as (select unnest(arr) as str),
sorted_w_len as (select str, length(str) as str_len
from unnestedorder by str_len desc),
sorted as (select str from sorted_w_len)
select array(select * from sorted) into ret;
return ret;
end;
$$;
Let's say you have the array named arr
: '{"one", "two"}'
.
select unnest(arr) as my_column_name;
will turn it into a table. And if you pass select array()
a table, it'll turn it into an array.