Differences

This shows you the differences between two versions of the page.

--- postgresqlfunctions [2022/11/23 14:52] – created z0hpvk
+++ postgresqlfunctions [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+===== SQL and PL/pgSQL =====
+==== JSON ====
+[[https://cdn.sanity.io/files/sczeoy4w/production/065818a8cd88cd048137368e5710ec35c0361185.pdf|| JSON Cheat Sheet]]
+==== Arrays ====
+Arrays can be displayed as ''ARRAY[1,2,3]'' or ''{1,2,3}''.\\
+=== Operators ===
+<code SQL>
+SELECT 1 WHERE 'ian' = ANY(ARRAY['ian','bob']); --True
+SELECT 1 WHERE 'ian' = ALL(ARRAY['ian','bob']); --False
+-- Does each element appearing in the 2nd array equal some element of the 1st array
+SELECT ARRAY[1,2,3] @> ARRAY[1,3];              --True
+SELECT ARRAY[1,2,3] @> ARRAY[1,3,4];            --False
+-- Does each element appearing in the 1st array equal some element of the 2nd array
+SELECT ARRAY[1,3] <@ ARRAY[1,2,3];              --True
+SELECT ARRAY[1,3,4] <@ ARRAY[1,2,3];            --False
+-- Do the arrays have any elements in common.
+SELECT ARRAY[1,2,3] && ARRAY[3,4];              --True
+SELECT ARRAY[1,2,3] && ARRAY[4,5];              --False
+</code>
+=== Append / Prepend ===
+<code SQL>
+SELECT ARRAY[1,2,3] || 4;
+SELECT array_append(ARRAY[1,2,3], 4);
+SELECT 0 || ARRAY[1,2,3];
+SELECT array_prepend(0, ARRAY[1,2,3]);
+</code>
+=== Replace / Remove ===
+<code SQL>
+SELECT array_replace(ARRAY[1,2,4], 4, 3);
+SELECT array_remove(ARRAY[1,2,3,4], 4);
+</code>
+=== Concatenate ===
+<code SQL>
+SELECT ARRAY[1,2,3] || 4;
+SELECT 0 || ARRAY[1,2,3];
+SELECT array_cat(ARRAY[0], ARRAY[1,2.3]);
+</code>
+=== Length ===
+<code SQL>
+SELECT array_length(ARRAY[1,2,3], 1);
+SELECT cardinality(ARRAY[1,2,3]);
+</code>
+=== Position ===
+<code SQL>
+SELECT array_position(ARRAY[1,2,3], 2);
+SELECT array_positions(ARRAY[1,2,2,3], 2);
+</code>
+=== Conversion ===
+<code SQL>
+SELECT array_to_string(ARRAY[1,2,3], ',');         -- Returns 1 row (1,2,3)
+SELECT unnest(ARRAY[1,2,3]);                       -- Returns 3 rows
+SELECT generate_subscripts(ARRAY['A','B','C'], 1); -- Returns 3 rows
+--Convert text column with comma separated values to array
+ALTER TABLE name_basics ALTER primaryprofession TYPE text[]
+                        USING string_to_array(primaryprofession, ',');
+</code>
+==== Hierarchical Data ====
+<code SQL>
+WITH RECURSIVE t1(n) AS (
+  VALUES (0)
+  UNION ALL
+  SELECT n+1 FROM t1 WHERE n < 5)
+SELECT * FROM t1;
+</code>
+<code SQL>
+CREATE TABLE family (key int, parent_key int, name text);
+INSERT INTO family VALUES (1, null, 'Grandad');
+INSERT INTO family VALUES (2, 1, 'Dad');
+INSERT INTO family VALUES (3, 2, 'Tracy');
+INSERT INTO family VALUES (4, 2, 'Ian');
+INSERT INTO family VALUES (5, 3, 'Michaela');
+INSERT INTO family VALUES (6, 3, 'Joshua');
+INSERT INTO family VALUES (7, 5, 'Freya');
+INSERT INTO family VALUES (8, 1, 'Colin');
+INSERT INTO family VALUES (9, 8, 'Mark');
+INSERT INTO family VALUES (10, 8, 'Simon');
+</code>
+<code SQL>
+WITH RECURSIVE a AS (
+  SELECT key, parent_key, name, 1::integer level, ARRAY[key] path, ' ' indent,
+         CASE WHEN EXISTS (SELECT 1 FROM family f2 WHERE f2.parent_key = f1.key )
+		      THEN 0 ELSE 1 END is_leaf
+  FROM   family f1
+  WHERE  parent_key IS NULL
+  UNION ALL
+  SELECT b.key, b.parent_key, a.indent || b.name, a.level +1, array_append(a.path, b.key), a.indent || ' ',
+         CASE WHEN EXISTS (SELECT 1 FROM family f2 WHERE f2.parent_key = b.key )
+		      THEN 0 ELSE 1 END is_leaf
+  FROM   family b
+  JOIN   a ON a.key = b.parent_key )
+SELECT key, parent_key, name, level, path, is_leaf FROM a ORDER BY path;
+</code>
+==== Templates ====
+=== Functions ===
 <code>
-CREATE OR REPLACE FUNCTION get_kpvid(kpvname text) RETURNS int
+CREATE [OR REPLACE] FUNCTION fnc_nme()
+   RETURNS <return type>
+   LANGUAGE plpgsql
+AS $$
+DECLARE
+-- Variables
+BEGIN
+ -- Logic
+END; $$
+</code>
+=== Procedures ===
+<code>
+CREATE [OR REPLACE] PROCEDURE prc_nme()
+LANGUAGE plpgsql
 AS $$
-#print_strict_params on
 DECLARE
-kpv_id int;
+-- Variables
 BEGIN
-    SELECT key_point_values.id INTO STRICT kpv_id
+-- Body
-        FROM key_point_values WHERE key_point_values.name = get_kpvid.kpvname;
+END; $$
-    RETURN kpv_id;
+</code>
-END;
-$$ LANGUAGE plpgsql;
+=== If Then Else ===
+<code>
+IF condition_1 THEN
+  statement_1;
+ELSIF condition_n THEN
+  statement_n;
+ELSE
+  else-statement;
+END IF;
+</code>
+=== Case ===
+<code>
+CASE RATE
+  WHEN 'a' THEN variable = 1;
+  WHEN 'b' THEN variable = 2;
+  ELSE PRICE_SEGMENT = 0;
+END CASE;
+CASE
+  WHEN total > 200 THEN variable = 'PLATINUM' ;
+  WHEN total > 100 THEN variable = 'GOLD' ;
+  ELSE variable = 'SILVER' ;
+END CASE;
+</code>
+=== Loop ===
+<code>
+LOOP
+   -- Statements
+   IF condition THEN
+      EXIT;
+   END IF;
+END LOOP;
+WHILE CONDITION LOOP
+   -- Statements;
+END LOOP;
+FOR loop_counter in [ reverse ] FROM.. TO [ by step ] LOOP
+    -- Statements
+END LOOP
 </code>