Help Center/ GaussDB(DWS)/ Standard Data Warehouse (8.1.3.x)/ Development and Design Proposal/ PL/pgSQL Usage
Updated on 2023-10-31 GMT+08:00
View PDF

PL/pgSQL Usage

General Principles

  1. Development shall strictly comply with design documents.
  2. Program modules shall be highly cohesive and loosely coupled.
  3. Proper, comprehensive troubleshooting measures shall be developed.
  4. Code shall be reasonable and clear.
  5. Program names shall comply with a unified naming rule.
  6. Fully consider the program efficiency, including the program execution efficiency and database query and storage efficiency. Use efficient and effective processing methods.
  7. Program comments shall be detailed, correct, and standard.
  8. The commit or rollback operation shall be performed at the end of a stored procedure, unless otherwise required by applications.
  9. Programs shall support 24/7 processing. In the case of an interruption, the applications shall provide secure, easy-to-use resuming features.
  10. Application output shall be standard and simple. The output shall show the progress, error description, and execution results for application maintenance personnel, and provide clear and intuitive reports and documents for business personnel.

Programming Principles

  1. Use bound variables in SQL statements in the PL/pgSQL.
  2. RETURNING is recommended for SQL statements in PL/pgSQL.
  3. Principles for using stored procedures:
    1. Do not use more than 50 output parameters of the Varchar or Varchar2 type in a stored procedure.
    2. Do not use the LONG type for input or output parameters.
    3. Use the CLOB type for output strings that exceed 10 MB.
  4. Variable declaration principles:
    1. Use %TYPE to declare a variable that has the same meaning as that of a column or variable in an application table.
    2. Use %ROWTYPE to declare a record that has the same meaning as that of a row in an application table.
    3. Each line of a variable declaration shall contain only one statement.
    4. Do not declare variables of the LONG type.
  5. Principles for using cursors:
    1. Explicit cursors shall be closed after being used.
    2. A cursor variable shall be closed after being used. If the cursor variable needs to transfer data to an invoked application, the cursor shall be closed in the application. If the cursor variable is used only in a stored procedure, the cursor shall be closed explicitly.
    3. Before using DBMS_SQL.CLOSE_CURSOR to close a cursor, use DBMS_SQL.IS_OPEN to check whether the cursor is open.
  6. Principles for collections:
    1. You are advised to use the FOR ALL statement instead of the FOR loop statement to reference elements in a collection.
  7. Principles for using dynamic statements:
    1. Dynamic SQL shall not be used in the transaction programs of online systems.
    2. Dynamic SQL statements can be used to implement DDL statements and system control commands in PL/pgSQL.
    3. Variable binding is recommended.
  8. Principles for assembling SQL statements:
    1. You are advised to use bound variables to assemble SQL statements.
    2. If the conditions for assembling SQL statements contain external input sources, the characters in the input conditions shall be checked to prevent attacks.
    3. In a PL/pgSQL script, the length of a single line of code cannot exceed 2499 characters.
  9. Principles for using triggers:
    1. Triggers can be used to implement availability design in scenarios where differential data logs are irrelevant to service processing.
    2. Do not use triggers to implement service processing functions.

Exception Handling Principles

Any error that occurs in a PL/pgSQL function aborts the execution of the function and related transactions. You can use a BEGIN block with an EXCEPTION clause to catch and fix errors.

  1. In a PL/pgSQL block, if an SQL statement cannot return a definite result, you are advised to handle exceptions (if any) in EXCEPTION. Otherwise, unhandled errors may be transferred to the external block and cause program logic errors.
  2. You can directly use the exceptions that have been defined in the system. DWS does not support custom exceptions.
  3. A block containing an EXCEPTION clause is more expensive to enter and exit than a block without one. Therefore, do not use EXCEPTION without need.

Writing Standard

  1. Variable naming rules:
    1. The input parameter format of a procedure or function is IN_Parameter_name. The parameter name shall be in uppercase.
    2. The output parameter format of a procedure or function is OUT_Parameter_name. The parameter name shall be in uppercase.
    3. The input and output parameter format of a procedure or function is IO_Parameter_name. The parameter name shall be in uppercase.
    4. Variables used in procedures and functions shall be composed of v_Variable_name. The variable name shall be in lower case.
    5. In query concatenation, the concatenation variable name of the WHERE statement shall be v_where, and the concatenation variable name of the SELECT statement shall be v_select.
    6. The record type (TYPE) name shall consist of T and a variable name. The name shall be in uppercase.
    7. A cursor name shall consist of CUR and a variable name. The name shall be in uppercase.
    8. The name of a reference cursor (REF CURSOR) shall consist of REF and a variable name. The name shall be in uppercase.
  2. Rules for defining variable types:
    1. Use %TYPE to declare the type of a variable that has the same meaning as that of a column in an application table.
    2. Use %ROWTYPE to declare the type of a record that has the same meaning as that of a row in an application table.
  3. Rules for writing comments:
    1. Comments shall be meaningful and shall not just repeat the code content.
    2. Comments shall be concise and easy to understand.
    3. Comments shall be provided at the beginning of each stored procedure or function. The comments shall contain a brief function description, author, compilation date, program version number, and program change history. The format of the comments at the beginning of stored procedures shall be the same.
    4. Comments shall be provided next to the input and output parameters to describe the meaning of variables.
    5. Comments shall be provided at the beginning of each block or large branch to briefly describe the function of the block. If an algorithm is used, comments shall be provided to describe the purpose and result of the algorithm.
  4. Variable declaration format:

    Each line shall contain only one statement. To assign initial values, write them in the same line.

  5. Letter case:

    Use uppercase letters except for variable names.

  6. Indentation:

    In the statements used for creating a stored procedure, the keywords CREATE, AS/IS, BEGIN, and END at the same level shall have the same indent.

  7. Statement rules:
    1. For statements that define variables, Each line shall contain only one statement.
    2. The keywords IF, ELSE IF, ELSE, and END at the same level shall have the same indent.
    3. The keywords CASE and END shall have the same indent. The keywords WHEN and ELSE shall be indented.
    4. The keywords LOOP and END LOOP at the same level shall have the same indent. Nested statements or statements at lower levels shall have more indent.