Skip to content

PL/pgSQL support #130

Description

@nene

General program structure

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ] ]
END [ label ];
  • Compiler options
    • #option dump
    • #print_strict_params { on | off }
    • #variable_conflict { error | use_variable | use_column }
  • Function parameters
    • $n variables are automatically assigned to function parameters.
  • DECLARE
    • useless extra DECLARE-s
    • ident datatype
      • [CONSTANT]
      • [COLLATE ident]
      • [NOT NULL]
      • [{= | := | DEFAULT} expr]
    • newname ALIAS FOR oldname;
    • newname ALIAS FOR $n;
    • name table.column%TYPE
    • name table_name%ROWTYPE
    • name RECORD
    • name CURSOR FOR query
      • SCROLL | NO SCROLL
      • ( arguments )
  • <<label>> is used for beginning labels before each statement (v/s label: in MySQL)
  • EXCEPTION
    • multiple WHEN ... THEN blocks
    • multiple condition [ OR condition ... ]
    • WHEN SQLSTATE 'code' THEN
    • WHEN condition_name THEN
    • The special condition_name others is parsed as identifier (like with all other condition_names), it can even be quoted.

Statements

  • NULL statement
  • Assignment
  • RETURN statement
    • RETURN NEXT expr
    • RETURN QUERY query
    • RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ]
  • CALL statement
  • Conditional statements
    • IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
    • CASE ... WHEN ... THEN ... ELSE ... END CASE
    • CASE WHEN ... THEN ... ELSE ... END CASE
  • Loops
    • LOOP ... END LOOP
    • EXIT [ label ]
      • [ WHEN boolean-expression ]
    • CONTINUE [ label ]
      • [ WHEN boolean-expression ]
    • WHILE expr LOOP ... END LOOP
    • FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP ... END LOOP
    • FOR target IN query LOOP ... END LOOP
      • SELECT query
      • INSERT, UPDATE, DELETE, or MERGE with a RETURNING clause.
    • FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP ... END LOOP
    • FOREACH target [ SLICE number ] IN ARRAY expression LOOP ... END LOOP
  • Raising errors
    • RAISE [ level ] 'format' [, expression [, ... ]] [ USING option { = | := } expression [, ... ] ];
    • RAISE [ level ] condition_name [ USING option { = | := } expression [, ... ] ];
    • RAISE [ level ] SQLSTATE 'sqlstate' [ USING option { = | := } expression [, ... ] ];
    • RAISE [ level ] USING option { = | := } expression [, ... ];
    • RAISE ;
    • Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION.
    • Using options are MESSAGE, DETAIL, HINT, ERRCODE, COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA.
  • Assertions
    • ASSERT condition [, message]

SQL statements

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions