Postgres Notes

Functions

Create a fuction that returns a text:

CREATE OR REPLACE FUNCTION build_username(first_name TEXT)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE prefix TEXT;
BEGIN
    prefix := 'user_' || first_name;
    return prefix;
END;
$BODY$;

Create a function that returns table:

CREATE OR REPLACE FUNCTION get_users(age INT)
RETURNS SETOF users 
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN query 
    SELECT u.*
    FROM users u
    where u.user_age = age;
END;
$BODY$;

Procedures

CREATE OR REPLACE PROCEDURE normalize_checklist_item_positions(
    IN in_checklist_id INT
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE public_id CHAR(24);
BEGIN
	public_id := get_checklist_public_id(in_checklist_id);
	CALL normalize_checklist_item_positions(public_id);
END
$BODY$;

Domains

To create a username domain that must be greater than 20 characters:

CREATE DOMAIN username 
CHECK (LENGTH(VALUE) > 20);

To create a domain with a regex constraint:

CREATE DOMAIN user_nanoid AS TEXT
CHECK (VALUE ~ '^clc_.{20}$');

Identity Columns

CREATE TABLE table_name (
    id INTEGER NOT NULL UNIQUE GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY PRIMARY KEY,
    column_name data_type,
    -- additional columns...
);

UUID

To generate a new UUID:

SELECT gen_random_uuid();   -- 7A168FE6-E9A0-4B18-9CDC-FADFD50B9C49

Row Types

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

In procedures or functions, you can declare variables to be of a specific table ROWTYPE.

For example, to access a single row in a procedure:

DECLARE event_row events%ROWTYPE;

SELECT * 
INTO event_row
FROM events
WHERE events.id = 3
LIMIT 1;

RAISE NOTICE 'selected event # %', event_row.id;

You can also use these types in a for loop:

DECLARE user_row users%ROWTYPE;

FOR user_row in 
    SELECT * FROM users
LOOP
    RAISE NOTICE 'current user id: %', user_row.id;
END LOOP;