IF Commons GitHub

Stored routines and procedural code

0006Active

0006-sql-routines · owner if · domain data

Where a statement ends

FR-001checkpassing

A statement that is procedural (a CREATE of a function, procedure, trigger, package or event, with or without OR REPLACE and a DEFINER; an anonymous block that begins BEGIN where BEGIN does not begin a transaction, or DECLARE; an IF or WHILE at the start of a statement) MUST end at the first ; that is outside every block it opens, where a block is opened by BEGIN, CASE, LOOP and REPEAT, and by IF where it begins a statement (after ;, THEN, ELSE, ELSIF, ELSEIF, BEGIN, LOOP or a label), and closed by END, a word after END (IF, LOOP, CASE, WHILE, REPEAT, FOR, TRY, CATCH, or a label) not opening another; a string, a quoted identifier, a dollar quote and a comment hide what they hold; and every other statement MUST end at its first ;, as before.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: a procedural statement ends at the first ; outside every block it opens, and every other statement at its first ;

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts a procedural statement ends at the first ; outside every block it opens, and every other s

FR-002checkpassing

BEGIN MUST begin a transaction, and end at its ;, where it is followed by ;, the end of the text, or one of TRANSACTION, WORK, DEFERRED, IMMEDIATE, EXCLUSIVE, ISOLATION, READ or NOT; elsewhere it begins a block.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: BEGIN begins a transaction where it is followed by ;, the end, or a word of a transaction, and a block elsewhere

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts BEGIN begins a transaction where it is followed by ;, the end, or a word of a transaction,

The header of a routine

FR-003checkpassing

CREATE [OR REPLACE] FUNCTION | PROCEDURE name (parameters) ... MUST be a node create-routine with its object, whether it replaces, its name, its parameters (each a node routine-param with its mode IN, OUT, INOUT or VARIADIC, its name, its type and its default), what it returns (a type, SETOF a type, or TABLE (columns)), its language, its other characteristics as written (IMMUTABLE, STRICT, SECURITY DEFINER, COST 100, SET x TO y and the rest), and its body; a header the grammar does not read MUST be a command, as any other CREATE of an object it does not read.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: CREATE FUNCTION and CREATE PROCEDURE are a create-routine node with the object, replace, name, parameters, result, language and characteristics, and a header that is not read is a command

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts CREATE FUNCTION and CREATE PROCEDURE are a create-routine node with the object, replace, n

FR-004checkpassing

The body MUST be a node routine-body with its style and its text: dollar (AS $$ ... $$), string (AS '...', with the text as the string's value and exact false where the source has escapes), block (BEGIN ... END, BEGIN ATOMIC ... END, or AS BEGIN ... END), return (RETURN expression, with the expression read) and external (AS 'object', 'symbol'); and for a dollar body, and a string or block body with nothing escaped, the offsets of the text between its delimiters in the whole text, so that what is in it can be read with its positions in the whole text.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: a routine's body is a node with its style and text, and its offsets in the whole text where the text can be read in place

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts a routine's body is a node with its style and text, and its offsets in the whole text wher

PL/pgSQL

FR-005checkpassing

parsePlpgsql MUST read the body of a routine whose language is PL/pgSQL (and a DO block) as a block: [<<label>>] [DECLARE declarations] BEGIN statements [EXCEPTION WHEN condition [OR condition] THEN statements] END [label], with declarations of variables (CONSTANT, NOT NULL, DEFAULT, :=, =, %TYPE, %ROWTYPE, ALIAS FOR) and cursors, and the statements assignment, IF/ELSIF/ELSE, CASE (searched and simple), LOOP, WHILE, FOR over an integer range and over a query, FOREACH, EXIT and CONTINUE with a label and WHEN, RETURN, RETURN NEXT, RETURN QUERY, RAISE, PERFORM, EXECUTE, OPEN, FETCH, MOVE, CLOSE, CALL, NULL, GET DIAGNOSTICS, ASSERT, COMMIT, ROLLBACK, a nested block, and a SQL statement.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: PL/pgSQL is read as a block of declarations, statements and handlers, with every statement the specification lists

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts PL/pgSQL is read as a block of declarations, statements and handlers, with every statement

Cited by: extensions/plpgsql/nodes, extensions/plpgsql/parse, Lang Sql

FR-006checkpassing

The expressions and SQL statements a body holds MUST be read with the core parser as nodes of the core tree with their positions in the whole text; an INTO [STRICT] targets of a SQL statement MUST be taken out of it and kept as the statement's targets; dynamic SQL (EXECUTE) MUST keep its command as an expression and its USING expressions.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: the SQL a body holds is read with the core parser at its place in the whole text, and the INTO of a statement is taken out of it and kept

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts the SQL a body holds is read with the core parser at its place in the whole text, and the

Cited by: extensions/plpgsql/nodes, extensions/plpgsql/parse, Lang Sql, parser

FR-007checkpassing

The comments of a body MUST be kept and attached to its nodes by the same rule as the comments of a script.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: the comments of a body are kept and attached to its nodes by the rule of the core

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts the comments of a body are kept and attached to its nodes by the rule of the core

Cited by: extensions/plpgsql/parse

FR-008checkpassing

parsePlpgsql MUST NOT throw; what it cannot read MUST be an error value with its span in the whole text, and the statement that holds it MUST be kept as a node of the kind pl-unparsed, so that the statements after it are read.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: what cannot be read is an error value with its span in the whole text, the statement is kept as unparsed or with a null in the place of the expression, and the statements after it are read

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts what cannot be read is an error value with its span in the whole text, the statement is ke

Cited by: extensions/plpgsql/parse, Lang Sql

FR-009checkpassing

Every routine of the corpus of PL/pgSQL functions (taken from PostgreSQL's regression tests) MUST be read without a throw, and the number whose body is read with no error MUST be held by a ratchet that only rises.

Enforced (check): .github: cd commons/lang-sql && bun run verify — suite/routines.test.ts: every routine of the corpus of PL/pgSQL from PostgreSQL's tests is read without a throw, and the number whose body is read with no error does not fall below the baseline

Proved by: lang-sql/suite/routines.test.ts

Tests: passed: suite/routines.test.ts every routine of the corpus of PL/pgSQL from PostgreSQL's tests is read without a throw, a

Out of scope

  • The bodies of routines of the other dialects (PL/SQL, T-SQL, MySQL, BigQuery and Snowflake scripting), which are read as block bodies without being read inside, and triggers, which are commands.
  • Checking that a variable is declared, that a type exists or that a label is used.

Edge cases

  • CREATE FUNCTION f() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; SELECT 1: two statements, per FR-001.
  • BEGIN; SELECT 1; COMMIT;: three statements, per FR-002.
  • DO $$ BEGIN PERFORM 1; END $$: one statement, a command whose body is read as a block, per FR-005.
  • CASE WHEN a THEN 1 END inside a body: opens and closes, per FR-001.
  • END IF; and END LOOP;: close one block each, per FR-001.
  • IF EXISTS in DROP TABLE IF EXISTS t;: not a block, per FR-001.
  • SELECT a INTO x FROM t in a body: the select without its INTO, and x as the targets, per FR-006.

Assumptions

  • A label is <<name>>; a loop's label is the one before it.
  • The rule of FR-001 is the common rule of the dialects with blocks, not each dialect's grammar: a procedural statement the rule ends wrongly becomes an error value like any other.

Open questions

OQ-1open question

Reading the bodies of the other dialects' routines: one extension each, by the same pattern, after this one.

Key entities

  • Routine - a function or procedure with its header and its body.
  • PL block - a block of PL/pgSQL with its declarations, statements and handlers.

Success criteria

SC-001success criterion

A script with a routine in it gives the routine as one statement, with its body found and, for PL/pgSQL, read.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

extensions/plpgsql/nodes, extensions/plpgsql/parse, Lang Sql, parser

Titles, headings, exports, tables, columns and requirements. Esc closes.