PolarDB:Basic statements

Assign a value

Use the following syntax to assign a value to a PL/SQL variable:

variable { := | = } expression;

The expression is evaluated by sending a SELECT statement to the primary database engine. The result must be a single value—or a row value if the target variable is a row or record variable.

The target variable can be:

• A simple variable (optionally qualified with a block name)

• A field of a row or record variable

• An array element of a simple variable or field

Both := and = are supported. The = form is provided for Oracle PL/SQL compatibility.

Type conversion: If the expression result type does not match the variable type, PL/SQL applies an assignment cast. If no assignment cast exists for the type pair, PL/SQL converts the result to a string using the output function of the result type, then applies the input function of the variable type. A runtime error occurs if the string form is rejected by the input function.

Examples:

tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;

Execute a statement with no result

For SQL statements that do not return rows—such as an INSERT without a RETURNING clause—write the statement directly in your PL/SQL function. PL/SQL variable names in the statement text are treated as parameters; their current values are substituted at execution time. The execution plan is cached and reused for subsequent calls.

Discard query results with PERFORM

To evaluate an expression or a SELECT query and discard the result—for example, to call a function that has side effects but no useful return value—use the PERFORM statement:

PERFORM query;

Write query exactly as you would write a SELECT statement, replacing the initial SELECT keyword with PERFORM. For WITH queries, use PERFORM and enclose the query in parentheses.

PL/SQL variables are substituted into the query and the execution plan is cached. FOUND is set to true if the statement returns at least one row, and false if no rows are returned.

Example:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

Execute a query that returns a single row

To assign the result of a single-row query to a variable, add an INTO clause to the statement:

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

target can be a record variable, a row variable, or a comma-separated list of simple variables and record or row fields.

PL/SQL variables are substituted into the rest of the query and the execution plan is cached. Supported statement types include SELECT, INSERT/UPDATE/DELETE with RETURNING clauses, and other statements that return a row set (such as EXPLAIN).

Column matching: If target is a row variable or a list of variables, the result columns must exactly match the target in number and data type. If target is a record variable, it automatically adapts to the row type of the query result.

Placement of the `INTO` clause: The INTO clause can appear almost anywhere in the SQL statement—typically before or after the select_expressions list in a SELECT, or at the end of other statement types. Follow this convention to ensure compatibility with future versions.

Behavior without `STRICT`

If STRICT is omitted, target is set to the first row returned, or to null if no rows are returned. Additional rows are silently discarded. "First row" is undefined unless you use ORDER BY. Check FOUND to determine whether a row was returned:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

Behavior with `STRICT`

If STRICT is specified, the query must return exactly one row. If it returns zero rows, NO_DATA_FOUND is raised. If it returns more than one row, TOO_MANY_ROWS is raised. Catch these errors with an exception block:

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

If a STRICT query succeeds, FOUND is set to true.

For INSERT, UPDATE, and DELETE with RETURNING clauses, an error is always raised if more than one row is returned—even without STRICT—because no ORDER BY is available to determine which row to return.

Debug STRICT failures with `print_strict_params`

Enable print_strict_params to include the query parameters in the error DETAIL when a STRICT condition is not met. Set it globally with the plpgsql.print_strict_params parameter (applies only to functions compiled after the change), or enable it per function with a compiler option:

CREATE FUNCTION get_userid(username text) RETURN int
IS
#print_strict_params on
DECLARE
    userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;

When the query returns no rows, the error message includes the parameter values:

ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/SQL function get_userid(text) line 6 at SQL statement

Execute a dynamic statement

Use EXECUTE to run a statement built at runtime—for example, when the table name or column name varies per call:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

• command-string: An expression of type text containing the SQL statement to run.

• target: A record variable, row variable, or comma-separated list of simple variables and record or row fields where results are stored.

• USING: Provides values to pass as parameters into the statement.

Key behavior differences from static SQL:

• PL/SQL variables are not substituted into the statement string automatically.

• No execution plan is cached. A new plan is generated on each call.

`INTO` clause behavior: If multiple rows are returned, only the first is assigned to target. If no rows are returned, null is assigned. If STRICT is specified, an error is raised unless exactly one row is returned.

When to use `EXECUTE` instead of static SQL

EXECUTE and equivalent static PL/SQL statements produce the same results, but differ in how execution plans are handled: PL/SQL creates a generic plan and caches it for reuse, while EXECUTE replans on every call based on current parameter values. When the optimal plan depends heavily on specific parameter values, use EXECUTE to prevent the engine from choosing a suboptimal generic plan.

Passing parameter values safely

Pass data values using parameter symbols ($1, $2, ...) referenced in the USING clause. This avoids runtime string conversion and protects against SQL injection:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Parameter symbols work only in SELECT, INSERT, UPDATE, and DELETE statements. For utility statements, insert data values as text.

Handling dynamic table or column names

Parameter symbols cannot be used for table or column names. Insert them as quoted identifiers using quote_ident or the %I specifier of format():

-- Using quote_ident with string concatenation
EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

-- Using format() — simpler and preferred
EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

Quoting dynamic values safely

Single-quote handling in dynamic statements requires care. Use format() with USING for the cleanest approach—variables are processed in their native data type format, avoiding forced text conversion:

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname)
   USING newvalue, keyvalue;

Alternatively, use the quoting functions directly:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

Use quote_nullable instead of quote_literal whenever the value might be null. If quote_literal receives a null argument, it returns null, making the entire dynamic query string null and causing EXECUTE to fail.

Null values in `WHERE` clauses

A WHERE clause using = never matches null:

-- This never matches when keyvalue is null
'WHERE key = ' || quote_nullable(keyvalue)

To handle null correctly, use IS NOT DISTINCT FROM:

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

Dollar quoting is not safe for dynamic values

Dollar quoting ($$) is useful for quoting fixed text in the function body, but must not be used for dynamic values. If the dynamic value contains the dollar-quoting delimiter, the statement fails. Always use quote_literal, quote_nullable, or quote_ident for values that are not known in advance.

Obtain the result status

Two methods are available to check the effect of a statement.

Method 1: `GET DIAGNOSTICS`

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

CURRENT is a noise word with no effect. Each item is a keyword identifying a status value to assign to variable. Use := or = interchangeably.

Available diagnostics items:

Example:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Method 2: `FOUND`

FOUND is a Boolean variable local to each PL/SQL function. Its initial value is false at the start of every function call. It is set as follows:

FOUND is not changed by EXECUTE. However, EXECUTE does update the output of GET DIAGNOSTICS.

FOUND is local to each function; changes in one function do not affect other functions.

Execute a placeholder statement

Use the NULL statement as a no-op placeholder—for example, to mark a deliberately empty branch in an if-then-else chain:

NULL;

The following two blocks behave identically:

-- Explicit NULL statement
BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- Ignores the error.
END;

-- Empty exception body
BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- Ignores the error.
END;

Choose based on your preference. Unlike Oracle PL/SQL—where empty statement lists are not allowed and NULL is required—PolarDB PL/SQL permits empty statements in both forms.