Frontier Software

Robert Laing's programing notes

PL/pgSQL

By Robert Laing

PL/pgSQL — SQL Procedural Language

Window Functions

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses.

window_function() OVER (PARTITION BY ...)

https://stackoverflow.com/questions/13040246/select-random-row-from-a-postgresql-table-with-weighted-row-probabilities

Errors and Messages

CREATE OR REPLACE FUNCTION

Set Returning Functions (SRFs)

FROM srf(inarg1, inarg2, … outarg1, outarg2, …)

CREATE OR REPLACE FUNCTION games_ancestors(game TEXT, d INTEGER)
RETURNS TABLE(a INTEGER) AS $$
BEGIN
RETURN QUERY EXECUTE format('
WITH RECURSIVE Ancestor(a, d) AS (
  SELECT init_id AS a, next_id AS d
  FROM %1$s_graph
  UNION
  SELECT Ancestor.a, next_id AS d
  FROM Ancestor, %1$s_graph
  WHERE Ancestor.d = init_id
)
SELECT a FROM Ancestor WHERE d = %2$L', game, d);
END;
$$ LANGUAGE plpgsql;

Table Returning Functions

https://learnsql.com/blog/get-to-know-the-power-of-sql-recursive-queries/

https://towardsdatascience.com/recursive-sql-queries-with-postgresql-87e2a453f1b

Common Table Expressions (CTEs)

JSONB

The basic value of SELECT in WITH is to break down complicated queries into simpler parts. An example is:

WITH bases AS (
  SELECT jsonb_array_elements(state) AS base 
  FROM tictactoe_states 
  WHERE id = 1
)
SELECT base->>1
FROM bases 
WHERE base->>0 = 'control';

Find the mobility for a given state

SELECT count(next_id)
FROM tictactoe_graph
WHERE init_id = 1;

Find the maximum utility for a given player

WITH bases AS (
  SELECT jsonb_array_elements(utility) AS base 
  FROM tictactoe_graph
  WHERE init_id = 1
)
SELECT max(base->>2)
FROM bases 
WHERE base->>1 = 'white';

Looping through Query Results

Graph Traversal

WITH RECURSIVE Ancestor(a, d) AS (
  SELECT init_id AS a, next_id AS d
  FROM tictactoe_graph
  UNION
  SELECT Ancestor.a, next_id AS d
  FROM Ancestor, tictactoe_graph
  WHERE Ancestor.d = init_id
)
SELECT a FROM Ancestor WHERE d = 108
ORDER BY a DESC;
WITH RECURSIVE Descendent(a, d) AS (
  SELECT init_id AS a, next_id AS d
  FROM tictactoe_graph
  UNION
  SELECT init_id AS a, Descendent.d
  FROM Descendent, tictactoe_graph
  WHERE Descendent.a = next_id
)
SELECT d FROM Descendent WHERE a = 55;
Last updated on 7 Jan 2021
Published on 7 Jan 2021

Content Footer