joelonsql

--
-- Content-Addressed Functions
--
-- Inspired by Unison's concept of using a hash of a function's syntax tree
-- as its name, sometimes referred to as "content-addressed naming",
-- we can avoid the need to create/drop lots of different temp function,
-- by using the hash of its definition as its name,
-- and simply reuse the function for all tests that need the same function.
--
-- While Unison uses 512-bit SHA3, we use sha224() since the maximum length

joelonsql

const std = @import("std");
const bigint = std.math.big.int.Managed;

pub fn main() !void {
    var a = try bigint.initSet(std.heap.c_allocator, 5);
    try a.pow(&a, try std.math.powi(u32, 4, try std.math.powi(u32, 3, 2)));
    defer a.deinit();

    var as = try a.toString(std.heap.c_allocator, 10, .lower);
    defer std.heap.c_allocator.free(as);

joelonsql

CREATE OR REPLACE FUNCTION tarjan(from_nodes int4range[], to_nodes int[])
RETURNS SETOF int[]
LANGUAGE plpgsql
AS
$$
--
-- https://en.wikipedia.org/wiki/Tarjan%27s_strongly_connected_components_algorithm
--
DECLARE
current_index integer := 0;

joelonsql

🗺🐘 1000+ PostgreSQL EXTENSIONs

This is a list of URLs to PostgreSQL EXTENSION repos, listed in alphabetical order of parent repo, with active forks listed under each parent.

⭐️ >= 10 stars
⭐️⭐️ >= 100 stars
⭐️⭐️⭐️ >= 1000 stars
Numbers of stars might not be up-to-date.

joelonsql

SQL language proposal: JOIN FOREIGN

The idea is to improve the SQL language, specifically the join syntax, for the special but common case when joining on foreign key columns.

The problem

Example below taken from PostgreSQL documentation [1]

In SQL-89, we didn't have any JOIN syntax yet, so queries were written in this way:

joelonsql

--
-- Here comes some general advise on how to use HyperLogLog
-- for someone who wants to implement a YouTube-like service
-- using the awesome https://github.com/citusdata/postgresql-hll PostgreSQL extension
--

CREATE TABLE counter (
id text NOT NULL, 
sketch hll NOT NULL DEFAULT hll_empty(),
PRIMARY KEY (id)

joelonsql

CREATE OR REPLACE FUNCTION guess_language(string text)
RETURNS text
LANGUAGE sql
AS
$$
SELECT cfgname::text FROM pg_ts_config WHERE cfgname <> 'simple' ORDER BY length(to_tsvector(cfgname::regconfig,string)) LIMIT 1
$$;

--
-- Demo testing some random sentences from Wikipedia:

joelonsql

CREATE OR REPLACE FUNCTION easter_plpgsql(year integer)
RETURNS date
LANGUAGE plpgsql
AS $$
-- https://github.com/christopherthompson81/pgsql_holidays/blob/master/utils/easter.pgsql
DECLARE
g CONSTANT integer := Year % 19;
c CONSTANT integer := Year / 100;
h CONSTANT integer := (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30;
i CONSTANT integer := h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11));

joelonsql

CREATE OR REPLACE FUNCTION Easter(Year integer)
RETURNS date
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- https://github.com/christopherthompson81/pgsql_holidays/blob/master/utils/easter.pgsql
DECLARE
g CONSTANT integer := Year % 19;
c CONSTANT integer := Year / 100;
h CONSTANT integer := (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30;

joelonsql

CREATE OR REPLACE FUNCTION decode_cbor(cbor bytea)
RETURNS TABLE (
  item integer,
  map_item_count integer,
  text_string text,
  bytes bytea
)
LANGUAGE sql
AS $$
/*