php_serialize.sql

--------------------------------------------------------------------------------
-- PHP-style serialization in PostgreSQL
-- Making a better situation of someone else's bad decision
-- See examples at bottom
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Serialization function per type

CREATE OR REPLACE FUNCTION php_serialize(boolean) RETURNS text
AS $$
	SELECT COALESCE('b:' || $1::int::text || ';', 'N;');
$$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize(integer) RETURNS text
AS $$
	SELECT COALESCE('i:' || $1::text || ';', 'N;');
$$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize(decimal) RETURNS text
AS $$
	SELECT COALESCE('d:' || $1::text || ';', 'N;');
$$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize(text) RETURNS text
AS $$
	SELECT COALESCE('s:' || char_length($1)::text || ':"' || $1 || '";', 'N;');
$$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize(anyarray) RETURNS text
AS $$
	SELECT CASE
		WHEN $1 IS NULL THEN 'N;'
		WHEN $1 = '{}' THEN 'a:0:{}'
		ELSE
			'a:' || array_length($1, 1)::text ||
			':{' ||
			(
				SELECT string_agg('i:' || i || ';' || php_serialize(v), '')
				FROM (
					SELECT (row_number() OVER ()) - 1, v FROM unnest($1) AS vals(v)
				) AS indexedvals(i, v)
			) ||
			'}'
	END;
$$
LANGUAGE SQL IMMUTABLE;

--------------------------------------------------------------------------------
-- Aggregation intermediate functions

CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], anyarray) RETURNS text[]
	AS $$ SELECT $1 ||  ('i:' || COALESCE(array_length($1, 1), 0) || ';' || php_serialize($2)); $$
	LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], anynonarray) RETURNS text[]
	AS $$ SELECT $1 ||  ('i:' || COALESCE(array_length($1, 1), 0) || ';' || php_serialize($2)); $$
	LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], integer, bool) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], integer, text) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], integer, integer) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], integer, decimal) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], integer, anyarray) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], text, bool) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], text, text) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], text, integer) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], text, decimal) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION php_serialize_sfunc(text[], text, anyarray) RETURNS text[]
	AS $$ SELECT array_append($1, php_serialize($2) || php_serialize($3)); $$
	LANGUAGE SQL IMMUTABLE;

--------------------------------------------------------------------------------
-- Aggregation final function

CREATE OR REPLACE FUNCTION php_serialize_finalfunc(text[]) RETURNS text
AS $$
	SELECT 'a:' || COALESCE(array_length($1, 1), 0) || ':{' || array_to_string($1, '') || '}';
$$
LANGUAGE SQL IMMUTABLE;

--------------------------------------------------------------------------------
-- Value aggregation

DROP AGGREGATE IF EXISTS php_agg(anyarray);
DROP AGGREGATE IF EXISTS php_agg(anynonarray);
CREATE AGGREGATE php_agg(anyarray)    (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(anynonarray) (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');

--------------------------------------------------------------------------------
-- Key, Value aggregation

DROP AGGREGATE IF EXISTS php_agg(integer, bool);
DROP AGGREGATE IF EXISTS php_agg(integer, text);
DROP AGGREGATE IF EXISTS php_agg(integer, integer);
DROP AGGREGATE IF EXISTS php_agg(integer, decimal);
DROP AGGREGATE IF EXISTS php_agg(integer, anyarray);
CREATE AGGREGATE php_agg(integer, bool)     (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(integer, text)     (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(integer, integer)  (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(integer, decimal)  (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(integer, anyarray) (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');

DROP AGGREGATE IF EXISTS php_agg(text, bool);
DROP AGGREGATE IF EXISTS php_agg(text, text);
DROP AGGREGATE IF EXISTS php_agg(text, integer);
DROP AGGREGATE IF EXISTS php_agg(text, decimal);
DROP AGGREGATE IF EXISTS php_agg(text, anyarray);
CREATE AGGREGATE php_agg(text, bool)     (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(text, text)     (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(text, integer)  (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(text, decimal)  (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');
CREATE AGGREGATE php_agg(text, anyarray) (sfunc = php_serialize_sfunc, stype = text[], finalfunc = php_serialize_finalfunc, initcond = '{}');

--------------------------------------------------------------------------------
-- Tests

SELECT
	php_serialize(42)                       = 'i:42;',
	php_serialize(42.42)                    = 'd:42.42;',
	php_serialize(true)                     = 'b:1;',
	php_serialize(false)                    = 'b:0;',
	php_serialize('hello')                  = 's:5:"hello";',
	php_serialize(NULL)                     = 'N;',
	php_serialize(ARRAY[]::int[])           = 'a:0:{}',
	php_serialize(ARRAY[]::bool[])          = 'a:0:{}',
	php_serialize(ARRAY[1, 2, 3])           = 'a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}',
	php_serialize(ARRAY[1, NULL, 3])        = 'a:3:{i:0;i:1;i:1;N;i:2;i:3;}',
	php_serialize(ARRAY[true, NULL, false]) = 'a:3:{i:0;b:1;i:1;N;i:2;b:0;}',
	php_serialize(ARRAY['a', NULL, 'c'])    = 'a:3:{i:0;s:1:"a";i:1;N;i:2;s:1:"c";}';

SELECT php_agg(v) = 'a:4:{i:0;b:1;i:1;b:0;i:2;b:0;i:3;b:1;}'
FROM (VALUES (true), (false), (false), (true)) AS t(v);

SELECT php_agg(v) = 'a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}'
FROM (VALUES (1), (2), (3)) AS t(v);

SELECT php_agg(v) = 'a:3:{i:0;d:1.1;i:1;d:2.22;i:2;d:3.333;}'
FROM (VALUES (1.1), (2.22), (3.333)) AS t(v);

SELECT php_agg(v) = 'a:3:{i:0;s:3:"one";i:1;s:3:"two";i:2;s:5:"three";}'
FROM (VALUES ('one'), ('two'),('three')) AS t(v);

SELECT php_agg(v) = 'a:2:{i:0;a:3:{i:0;i:1;i:1;i:2;i:2;i:3;}i:1;a:3:{i:0;i:4;i:1;i:5;i:2;i:6;}}'
FROM (VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6])) AS t(v);

SELECT php_agg(v) = 'a:2:{i:0;a:2:{i:0;s:3:"one";i:1;s:3:"two";}i:1;a:2:{i:0;s:5:"three";i:1;s:4:"four";}}'
FROM (VALUES (ARRAY['one', 'two']), (ARRAY['three', 'four'])) AS t(v);

SELECT php_agg(v) = 'a:3:{i:0;a:1:{i:0;s:1:"a";}i:1;N;i:2;a:0:{}}'
FROM (VALUES (ARRAY['a']), (NULL), ('{}'::text[])) AS t(v);

SELECT php_agg(k, v) = 'a:2:{i:2;i:4;i:4;i:8;}'
FROM (VALUES (2, 4), (4, 8)) AS t(k, v);

SELECT php_agg(k, v) = 'a:2:{s:3:"php";b:0;s:7:"clojure";b:1;}'
FROM (VALUES ('php', false), ('clojure', true)) AS t(k, v);

SELECT php_agg(k, v) = 'a:2:{s:2:"k1";a:2:{i:0;s:3:"foo";i:1;s:3:"bar";}s:2:"k2";a:2:{i:0;s:3:"baz";i:1;s:3:"qux";}}'
FROM (VALUES ('k1', ARRAY['foo', 'bar']), ('k2', ARRAY['baz', 'qux'])) AS t(k, v);

SELECT php_agg(v) = 'a:0:{}' FROM unnest('{}'::int[]) AS t(v);

--------------------------------------------------------------------------------
-- Cleanup

-- DROP AGGREGATE IF EXISTS php_agg(text, bool);
-- DROP AGGREGATE IF EXISTS php_agg(text, text);
-- DROP AGGREGATE IF EXISTS php_agg(text, integer);
-- DROP AGGREGATE IF EXISTS php_agg(text, decimal);
-- DROP AGGREGATE IF EXISTS php_agg(text, anyarray);
-- DROP AGGREGATE IF EXISTS php_agg(integer, bool);
-- DROP AGGREGATE IF EXISTS php_agg(integer, text);
-- DROP AGGREGATE IF EXISTS php_agg(integer, integer);
-- DROP AGGREGATE IF EXISTS php_agg(integer, decimal);
-- DROP AGGREGATE IF EXISTS php_agg(integer, anyarray);
-- DROP AGGREGATE IF EXISTS php_agg(anyarray);
-- DROP AGGREGATE IF EXISTS php_agg(anynonarray);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], anyarray);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], anynonarray);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], integer, bool);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], integer, text);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], integer, integer);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], integer, decimal);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], integer, anyarray);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], text, bool);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], text, text);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], text, integer);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], text, decimal);
-- DROP FUNCTION IF EXISTS php_serialize_sfunc(text[], text, anyarray);
-- DROP FUNCTION IF EXISTS php_serialize_finalfunc(text[]);
-- DROP FUNCTION IF EXISTS php_serialize(boolean);
-- DROP FUNCTION IF EXISTS php_serialize(integer);
-- DROP FUNCTION IF EXISTS php_serialize(decimal);
-- DROP FUNCTION IF EXISTS php_serialize(text);
-- DROP FUNCTION IF EXISTS php_serialize(anyarray);