This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL function to convert numbers from custom bases to numeric | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION number_from_base(num TEXT, base INTEGER) | |
RETURNS NUMERIC | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT sum(exp * cn) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL function to test if two json values are equal | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION json_equals(json, json) | |
RETURNS BOOLEAN | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT CASE f1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Aggregate function to aggregate key-value pairs to json object (opposite of json_each()) | |
-- requires PostgreSQL 9.3+ (but < 9.4!) | |
-- requires function "json_object_set_key" | |
DROP AGGREGATE IF EXISTS "json_object_agg" (TEXT, anyelement); | |
CREATE AGGREGATE "json_object_agg" (TEXT, anyelement) | |
( | |
STYPE = json, | |
SFUNC = "json_object_set_key", |