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
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.
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"]') );
with expressions, we:
unnestthe array into a table
arrayto 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
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.