Thread: Formal Syntax of PL/pgSQL

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/index.html
Description:

I am currently working on a project that involves analyzing and building AST
(Abstract Syntax Tree) interfaces for PL/pgSQL in TypeScript. My goal is to
model the entire syntax of PL/pgSQL comprehensively, covering all its
constructs exactly as they are understood syntactically by PostgreSQL when
processing functions or procedures.

While exploring the official documentation, I noticed that commands like
CREATE TABLE or SELECT are documented with clear and formalized syntax
structures, which are incredibly helpful for developers needing to parse or
programmatically interact with these commands. However, for PL/pgSQL, I
couldn't find a similar consolidated syntax reference. For instance, while
the documentation explains the supported declarations, control structures,
and statements, it lacks a unified formal syntax block that encapsulates the
entire language as PostgreSQL interprets it.

Would it be possible to provide such a formal syntax reference for PL/pgSQL,
or point me toward an existing resource that captures this? Having access to
a detailed and comprehensive syntax description would be immensely helpful
for projects requiring precise parsing and manipulation of PL/pgSQL code.

Thank you for your time and for the incredible work you do on PostgreSQL.

On Thu, Dec  5, 2024 at 02:33:16PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/17/index.html
> Description:
> 
> I am currently working on a project that involves analyzing and building AST
> (Abstract Syntax Tree) interfaces for PL/pgSQL in TypeScript. My goal is to
> model the entire syntax of PL/pgSQL comprehensively, covering all its
> constructs exactly as they are understood syntactically by PostgreSQL when
> processing functions or procedures.
> 
> While exploring the official documentation, I noticed that commands like
> CREATE TABLE or SELECT are documented with clear and formalized syntax
> structures, which are incredibly helpful for developers needing to parse or
> programmatically interact with these commands. However, for PL/pgSQL, I
> couldn't find a similar consolidated syntax reference. For instance, while
> the documentation explains the supported declarations, control structures,
> and statements, it lacks a unified formal syntax block that encapsulates the
> entire language as PostgreSQL interprets it.
> 
> Would it be possible to provide such a formal syntax reference for PL/pgSQL,
> or point me toward an existing resource that captures this? Having access to
> a detailed and comprehensive syntax description would be immensely helpful
> for projects requiring precise parsing and manipulation of PL/pgSQL code.

I doubt it since no one has ever asked for this before, and we usually
tell them to look at the plpgsql parser if they want those details.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

PG Doc comments form <noreply@postgresql.org> writes:
> While exploring the official documentation, I noticed that commands like
> CREATE TABLE or SELECT are documented with clear and formalized syntax
> structures, which are incredibly helpful for developers needing to parse or
> programmatically interact with these commands. However, for PL/pgSQL, I
> couldn't find a similar consolidated syntax reference. For instance, while
> the documentation explains the supported declarations, control structures,
> and statements, it lacks a unified formal syntax block that encapsulates the
> entire language as PostgreSQL interprets it.

I'm not following your complaint.  Each statement type in plpgsql
is described with a <synopsis> that seems to me to have about the
same level of rigor as those given in the SQL command reference
pages.  There are exceptions such as SELECT INTO, but doing
something else there would entail duplicating the whole SELECT
syntax synopsis (not to mention INSERT, UPDATE, and DELETE).
That seems unreasonable, plus it would be a lie, because reality
is that the INTO clause doesn't have to appear directly after
the SELECT or RETURNING targetlist.

> Would it be possible to provide such a formal syntax reference for PL/pgSQL,
> or point me toward an existing resource that captures this? Having access to
> a detailed and comprehensive syntax description would be immensely helpful
> for projects requiring precise parsing and manipulation of PL/pgSQL code.

The underlying truth here is that plpgsql's syntax is a good deal
less formal than the core SQL engine's.  The core engine uses a
bison+flex parser and is constrained by the limitations of those
tools.  While plpgsql also uses a bison parser, it cheats like mad
by bailing out of that parser to handle various constructs (notably,
any core-SQL statement or expression) with handwritten code.  So it's
inherently squishier than the core.  The documentation has to reflect
that.  I fear a project built on the assumption that it's not squishy
is doomed to failure --- you might get most of the way there, but
there will be input that plpgsql will accept that you won't.

            regards, tom lane