sqids-plpgsql
sqids-plpgsql copied to clipboard
mark functions as immutable
When using the output of squids.encode(...)
in a GENERATED
column, postgres was complaining that the functions needs to be immutable.
-- Example Table
CREATE TABLE public.order(
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
external_id text UNIQUE NOT NULL GENERATED ALWAYS AS (sqids.encode(ARRAY[id, 0], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 5)) STORED,
amount numeric NOT NULL,
name text,
created_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL,
updated_at timestamp with time zone DEFAULT timezone('utc', now()) NOT NULL
);
From the postgres documentation:
An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE.
TL;DR Functions marked as IMMUTABLE always return the same output for the same input and do not depend on any external state. Non-IMMUTABLE functions may depend on database state or have side effects.
Accordingly, I've marked the following functions as follows:
Function | IMMUTABLE |
---|---|
sqids.shuffle |
Yes |
sqids.checkAlphabet |
Yes |
sqids.toId |
Yes |
sqids.toNumber |
Yes |
sqids.encodeNumbers |
Yes |
sqids.decode |
Yes |
sqids.encode (both versions) |
Yes |
sqids.isBlockedId |
No |
sqids.defaultBlocklist |
No |
The tests included with the repo all pass locally on my machine with these changes.