X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fpg_jsonb_opx;a=blobdiff_plain;f=jsonb_opxi_sql_comparison.sql;fp=jsonb_opxi_sql_comparison.sql;h=4bfebf610de4d66bbbecee023bec314e9b1f19a7;hp=0000000000000000000000000000000000000000;hb=8c1743c63b561e0c49fb41be3f837eac46d54aad;hpb=62640eaa675407c0c2ca3ff7d74902df1aaa3583

diff --git a/jsonb_opxi_sql_comparison.sql b/jsonb_opxi_sql_comparison.sql
new file mode 100755
index 0000000..4bfebf6
--- /dev/null
+++ b/jsonb_opxi_sql_comparison.sql
@@ -0,0 +1,59 @@
+-- The functions in this script are SQL versions of the C ones for comparison
+-- of performance between the two.
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text)
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE key <> b
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE key <> ALL(b)
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
+RETURNS jsonb AS
+$BODY$
+SELECT COALESCE(
+(
+SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
+FROM jsonb_each(a)
+WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b
+)
+, '{}')::jsonb;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;
+
+--
+
+CREATE OR REPLACE FUNCTION jsonb_concat_left (a jsonb, b jsonb) 
+RETURNS jsonb AS
+$BODY$
+SELECT json_object_agg(key, value)::jsonb FROM
+(
+  SELECT * FROM jsonb_each(a)
+  UNION ALL
+  SELECT * FROM jsonb_each(b)
+) a;
+$BODY$
+LANGUAGE sql IMMUTABLE STRICT;