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:

0. unnest the array into a table
1. create a table with the string and lengths and use that to order the table
2. output the ordered table without the lengths; and finally
3. use array 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.

It's easy enough

replace('hello', 'llo', 'lp');

And this will return help.