===== 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 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 $$
DECLARE
-- Variables
BEGIN
-- Body
END; $$
</code>

=== 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>