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 $$
/*

joelonsql

CREATE OR REPLACE FUNCTION decode_asn1_der(asn1der bytea)
RETURNS TABLE (
  type_tag char(2),
  length integer,
  value bytea
)
LANGUAGE sql
AS $$
WITH RECURSIVE X AS (
  SELECT

joelonsql

#!/usr/bin/perl
#
# jpipe2sprintf
#
# Script to convert Bucklescript string interpolation [1]
# to normal OCaml using Printf.sprintf.
#
# Note: It replaces any such occurrences, even inside comments.
# If anyone would like to fix this script to leave comments
# untouched, such a patch would be most welcomed.

joelonsql

(* Original OCaml code: *)

let flatten_transitions : StateSet.t CharSetMap.t -> StateSet.t CharMap.t =
  fun char_map ->
    CharSetMap.fold
      (fun char_set state_set char_map ->
         CharSet.fold
           (fun char char_map ->
              let entry = match CharMap.find char char_map with
                | exception Not_found -> StateSet.empty