DB Wiki

User Tools

Site Tools

Trace: • postgresqlfunctions • postgresqlvacuum • terraformpostgresql
postgresqlfunctions

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
postgresqlfunctions [2024/03/01 09:33] – z0hpvkpostgresqlfunctions [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 1: Line 1:
-===== Templates =====+===== SQL and PL/pgSQL =====
  
-==== Functions ====+==== 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> <code>
 CREATE [OR REPLACE] FUNCTION fnc_nme() CREATE [OR REPLACE] FUNCTION fnc_nme()
Line 14: Line 122:
 </code> </code>
  
-==== Procedures ====+=== Procedures ===
 <code> <code>
 CREATE [OR REPLACE] PROCEDURE prc_nme() CREATE [OR REPLACE] PROCEDURE prc_nme()
Line 26: Line 134:
 </code> </code>
  
-==== If Then Else ====+=== If Then Else ===
 <code> <code>
 IF condition_1 THEN IF condition_1 THEN
Line 37: Line 145:
 </code> </code>
  
-==== Case ====+=== Case ===
 <code> <code>
 CASE RATE CASE RATE
Line 52: Line 160:
 </code> </code>
  
-==== Loop ====+=== Loop ===
 <code> <code>
 LOOP LOOP
postgresqlfunctions.1709285625.txt.gz · Last modified: 2025/03/08 22:23 (external edit)