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

diff --git a/sql/jsonb_opx_sql_comparison.sql b/sql/jsonb_opx_sql_comparison.sql
new file mode 100755
index 0000000..4bfebf6
--- /dev/null
+++ b/sql/jsonb_opx_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;