PL/pgSQL Functions in version 7.0

Use cases

Limits

The PL/pgSQL implementation in AnalyticDB for PostgreSQL differs from standard PostgreSQL in the following ways:

Block structure

PL/pgSQL code is organized into blocks. Each block contains a declaration section, a statement section, and an optional exception-handling section. The syntax is as follows:

[ <<label>> ]
[ DECLARE
    -- Declaration section
    declarations ]
BEGIN
    -- Statement section
    statements
[ EXCEPTION
    -- Exception-handling section
    WHEN condition THEN
        handler_statements ]
END [ label ];

Example: Basic block structure

CREATE FUNCTION simple_example() RETURNS integer AS $$
DECLARE
    counter integer := 0;
BEGIN
    counter := counter + 1;
    RETURN counter;
END;
$$ LANGUAGE plpgsql;

Example: Nested blocks

Blocks can be nested. Inner blocks can access variables from outer blocks.

CREATE FUNCTION nested_blocks() RETURNS void AS $$
<<outer_block>>
DECLARE
    outer_var integer := 1;
BEGIN
    RAISE NOTICE 'outer_var = %', outer_var;
    
    <<inner_block>>
    DECLARE
        inner_var integer := 2;
    BEGIN
        RAISE NOTICE 'inner_var = %', inner_var;
        RAISE NOTICE 'outer_var from inner = %', outer_var;
    END inner_block;
    
END outer_block;
$$ LANGUAGE plpgsql;

Labels (<<label>>) serve the following purposes:

• Specify target blocks or loops in EXIT and CONTINUE statements.

• Disambiguate variables with the same name by using the label.variable syntax.

Comments

PL/pgSQL supports two comment styles:

-- Single-line comment
x := 1;  -- End-of-line comment

/*
 * Multi-line comment
 * Can span multiple lines
 */
x := 1;

Variable declarations

Declare variables in the DECLARE section by using the following syntax:

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

Examples

DECLARE
    user_id integer;                          -- Basic declaration
    quantity integer := 10;                   -- With default value
    tax_rate numeric(5,2) DEFAULT 0.08;       -- Using DEFAULT keyword
    PI CONSTANT numeric := 3.14159265;        -- Constant
    counter integer NOT NULL := 0;            -- NOT NULL constraint (default value required)
    greeting text := 'Hello, World!';         -- String variable

Data types

PL/pgSQL supports all PostgreSQL base data types and provides the following specialized types:

• %TYPE: Reference a column type. Use %TYPE to reference the data type of a table column. The function automatically adapts when the table structure changes.

  DECLARE
      emp_name employees.name%TYPE;
      emp_salary employees.salary%TYPE;

• %ROWTYPE: Reference a row type. Use %ROWTYPE to declare a record variable that matches a table structure.

  DECLARE
      emp_row employees%ROWTYPE;
  BEGIN
      SELECT * INTO emp_row FROM employees WHERE id = 1;
      RAISE NOTICE 'Employee: %, Salary: %', emp_row.name, emp_row.salary;
  END;

• RECORD: Dynamic record type. A RECORD variable has its structure determined at runtime, which is suitable for dynamic query scenarios.

  DECLARE
      rec RECORD;
  BEGIN
      FOR rec IN SELECT id, name FROM employees LOOP
          RAISE NOTICE 'ID: %, Name: %', rec.id, rec.name;
      END LOOP;
  END;

• Array types: One-dimensional and multi-dimensional arrays are supported.

  DECLARE
      int_array integer[];
      str_array text[] := ARRAY['a', 'b', 'c'];
      numbers integer[] := '{1, 2, 3, 4, 5}';
  BEGIN
      RAISE NOTICE 'First element: %', str_array[1];  -- Index starts at 1
      int_array := int_array || 10;                    -- Append element
      RAISE NOTICE 'Array length: %', array_length(str_array, 1);
  END;

• Composite types: Custom structured types are supported.

  -- Create a custom type
  CREATE TYPE person_type AS (
      name text,
      age integer,
      email text
  );
  
  -- Use in a function
  DECLARE
      person person_type;
  BEGIN
      person.name := 'John';
      person.age := 30;
      person.email := 'john@example.com';
  END;

Function parameters

Function parameters can be referenced in the following ways:

-- Method 1: Named parameters (recommended)
CREATE FUNCTION add_nums(a integer, b integer) RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Method 2: Positional parameters
CREATE FUNCTION add_nums(integer, integer) RETURNS integer AS $$
BEGIN
    RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

-- Method 3: ALIAS FOR
CREATE FUNCTION add_nums(integer, integer) RETURNS integer AS $$
DECLARE
    first_num ALIAS FOR $1;
    second_num ALIAS FOR $2;
BEGIN
    RETURN first_num + second_num;
END;
$$ LANGUAGE plpgsql;

Assignments

PL/pgSQL supports the following assignment methods:

• := assignment operator: Assign a value directly to a variable.

  DECLARE
      x integer;
      y integer;
      total integer;
  BEGIN
      x := 10;
      y := 20;
      total := x + y;
  END;

• SELECT INTO assignment: Assign query results to variables by using SELECT INTO.

  DECLARE
      emp_name text;
      emp_salary numeric;
      emp_count integer;
  BEGIN
      -- Single-value assignment
      SELECT name INTO emp_name FROM employees WHERE id = 1;
      
      -- Multi-value assignment
      SELECT name, salary INTO emp_name, emp_salary 
      FROM employees WHERE id = 1;
      
      -- Aggregate result assignment
      SELECT count(*) INTO emp_count FROM employees;
      
      -- Strict mode: requires exactly one row, otherwise raises an exception
      SELECT name INTO STRICT emp_name FROM employees WHERE id = 1;
  END;

• FOUND variable: FOUND is a Boolean variable that indicates whether the last SQL statement affected any rows.

  DECLARE
      emp_name text;
  BEGIN
      SELECT name INTO emp_name FROM employees WHERE id = 999;
      
      IF NOT FOUND THEN
          RAISE NOTICE 'Employee not found';
      ELSE
          RAISE NOTICE 'Found: %', emp_name;
      END IF;
  END;

Expression evaluation

Expressions in PL/pgSQL use standard SQL expression syntax and support mathematical operations, string operations, conditional evaluation, and SQL function calls.

DECLARE
    result numeric;
    condition boolean;
BEGIN
    -- Mathematical expression
    result := 10 + 20 * 3;
    
    -- String expression
    result := length('Hello') + length('World');
    
    -- Conditional expression
    condition := (result > 100) AND (result < 200);
    
    -- SQL function call
    result := abs(-50) + round(3.7);
END;

Expression caching (plan caching)

PL/pgSQL automatically caches execution plans for SQL statements to improve repeated execution efficiency. Be aware of the following considerations:

-- A cached plan may not be optimal when parameter values vary widely
CREATE FUNCTION get_employee(emp_id integer) RETURNS text AS $$
DECLARE
    result text;
BEGIN
    -- This query plan is cached (may not be optimal if emp_id varies widely)
    SELECT name INTO result FROM employees WHERE id = emp_id;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

The volatility classification of a function (IMMUTABLE/STABLE/VOLATILE) affects how the database caches query plans that reference the function.

• IMMUTABLE: A pure function that always returns the same output for the same input. The optimizer can evaluate the result at planning time (constant folding). Does not depend on database content, configuration, or time. Use cases: mathematical calculations, string formatting, fixed-rule conversions.

  CREATE FUNCTION celsius_to_fahrenheit(c numeric) RETURNS numeric AS $$
  BEGIN
      RETURN c * 9.0 / 5.0 + 32;
  END;
  $$ LANGUAGE plpgsql IMMUTABLE;

• STABLE: Returns the same result within a single table scan. Can read the database, but the result does not change within the same SQL statement execution. The optimizer can cache the result within a single scan to avoid repeated calls. Can be used in index conditions (WHERE clause of Index Scan). Use cases: reading configuration tables, queries based on the current transaction snapshot, time zone conversions.

  CREATE FUNCTION get_config_value(key text) RETURNS text AS $$
  DECLARE
      val text;
  BEGIN
      SELECT value INTO val FROM config_table WHERE config_key = key;
      RETURN val;
  END;
  $$ LANGUAGE plpgsql STABLE;

• VOLATILE: May return different results on each call. Can modify database state (INSERT/UPDATE/DELETE). The optimizer does not cache or optimize away any calls. Cannot be used in index conditions. This is the default. Use cases: data modifications, operations that depend on random numbers or current time, operations with side effects.

  CREATE FUNCTION log_access(user_id integer) RETURNS void AS $$
  BEGIN
      INSERT INTO access_log VALUES (user_id, now());
  END;
  $$ LANGUAGE plpgsql VOLATILE;

Type casting

DECLARE
    str_val text := '123';
    int_val integer;
    float_val numeric;
BEGIN
    -- Explicit type casting
    int_val := str_val::integer;
    float_val := CAST(str_val AS numeric);
    
    -- Conversion function
    int_val := to_number(str_val, '999');
END;

PERFORM

PERFORM executes a query when you do not need the result. The syntax is the same as SELECT, but the PERFORM keyword replaces SELECT.

BEGIN
    -- Call a function
    PERFORM my_procedure(10);
    
    -- Execute an operation with side effects
    PERFORM pg_sleep(1);
    
    -- Check whether data exists
    PERFORM 1 FROM employees WHERE id = 100;
    IF FOUND THEN
        RAISE NOTICE 'Employee exists';
    END IF;
END;

EXECUTE

EXECUTE runs dynamically constructed SQL statements. Use EXECUTE when table names, column names, or query conditions must be determined at runtime.

• Pass parameters with USING: Use the USING clause to pass parameter values safely.

  DECLARE
      min_salary numeric := 50000;
      dept_id integer := 10;
      emp_count integer;
  BEGIN
      EXECUTE 'SELECT count(*) FROM employees 
               WHERE salary > $1 AND department_id = $2'
          INTO emp_count
          USING min_salary, dept_id;
      
      RAISE NOTICE 'Count: %', emp_count;
  END;

• Prevent SQL injection: When you dynamically construct SQL, you must escape user input.

  -- Bad: Direct concatenation of user input. This is a SQL injection risk.
  EXECUTE 'SELECT * FROM ' || user_input;
  
  -- Good: Use quote_ident for identifiers and quote_literal for values
  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) 
      || ' WHERE name = ' || quote_literal(user_name);
  
  -- Recommended: Use the format function (%I = identifier, %L = literal)
  EXECUTE format('SELECT * FROM %I WHERE name = %L', 
                 table_name, user_name);
  
  -- Recommended: Use USING to pass parameter values
  EXECUTE 'SELECT * FROM employees WHERE name = $1'
      USING user_name;

RETURN

PL/pgSQL functions support the following return methods:

• Return a single value: Use RETURN to return a scalar value.

  CREATE FUNCTION get_sum(a integer, b integer) RETURNS integer AS $$
  BEGIN
      RETURN a + b;
  END;
  $$ LANGUAGE plpgsql;

• RETURN NEXT and RETURN QUERY: Return a result set.

  -- RETURN NEXT: Return rows one at a time
  CREATE FUNCTION get_numbers(n integer) RETURNS SETOF integer AS $$
  DECLARE
      i integer;
  BEGIN
      FOR i IN 1..n LOOP
          RETURN NEXT i;
      END LOOP;
      RETURN;
  END;
  $$ LANGUAGE plpgsql;
  
  -- RETURN QUERY: Return a query result set
  CREATE FUNCTION get_employees(dept_id integer) 
  RETURNS SETOF employees AS $$
  BEGIN
      RETURN QUERY SELECT * FROM employees WHERE department_id = dept_id;
      RETURN;
  END;
  $$ LANGUAGE plpgsql;
  
  -- RETURN QUERY EXECUTE: Return a dynamic query result set
  CREATE FUNCTION get_by_table(tbl text) RETURNS SETOF RECORD AS $$
  BEGIN
      RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(tbl);
  END;
  $$ LANGUAGE plpgsql;

RAISE

RAISE outputs debugging information or raises exceptions.

• Message levels: RAISE supports the following message levels.

  Level	Description
  DEBUG	Debug information, not displayed by default.
  LOG	Log information.
  INFO	General information.
  NOTICE	Notice information, displayed by default.
  WARNING	Warning information.
  EXCEPTION	Raises an exception, interrupts execution, and rolls back the current transaction.

• Format messages: Use % as a placeholder. Placeholders are replaced by the subsequent arguments in order.

  DECLARE
      user_name text := 'John';
      user_age integer := 30;
  BEGIN
      RAISE NOTICE 'User % is % years old', user_name, user_age;
      -- Output: NOTICE:  User John is 30 years old
      
      RAISE NOTICE 'Completion: %%100';  -- Use %% to output a percent sign
  END;

• Raise custom exceptions: Use the USING clause to specify error details.

  BEGIN
      RAISE EXCEPTION 'Invalid input'
          USING ERRCODE = 'invalid_parameter_value',
                DETAIL = 'The value must be positive',
                HINT = 'Provide a positive integer';
  END;

Conditional statements

PL/pgSQL supports the following conditional statements:

• IF statements: Support IF...THEN...ELSIF...ELSE...END IF structure.

  DECLARE
      grade integer := 85;
      result text;
  BEGIN
      IF grade >= 90 THEN
          result := 'A';
      ELSIF grade >= 80 THEN
          result := 'B';
      ELSIF grade >= 70 THEN
          result := 'C';
      ELSIF grade >= 60 THEN
          result := 'D';
      ELSE
          result := 'F';
      END IF;
      
      RAISE NOTICE 'Grade: %', result;
  END;

• CASE expressions: Support simple CASE and searched CASE forms.

  -- Simple CASE
  DECLARE
      status integer := 1;
      status_text text;
  BEGIN
      status_text := CASE status
          WHEN 0 THEN 'Inactive'
          WHEN 1 THEN 'Active'
          WHEN 2 THEN 'Pending'
          ELSE 'Unknown'
      END;
  END;
  
  -- Searched CASE
  DECLARE
      age integer := 25;
      category text;
  BEGIN
      category := CASE
          WHEN age < 13 THEN 'Child'
          WHEN age < 20 THEN 'Teenager'
          WHEN age < 60 THEN 'Adult'
          ELSE 'Senior'
      END;
  END;

Loop statements

PL/pgSQL supports the following loop statements:

• LOOP: An infinite loop that requires EXIT to terminate.

  DECLARE
      counter integer := 0;
  BEGIN
      LOOP
          counter := counter + 1;
          RAISE NOTICE 'Counter: %', counter;
          EXIT WHEN counter >= 5;
      END LOOP;
  END;

• WHILE loop: Loops while the condition is true.

  DECLARE
      counter integer := 1;
  BEGIN
      WHILE counter <= 5 LOOP
          RAISE NOTICE 'Counter: %', counter;
          counter := counter + 1;
      END LOOP;
  END;

• FOR loop (integer range): Iterates over an integer range. Supports forward, reverse, and custom step values.

  BEGIN
      -- Forward iteration
      FOR i IN 1..5 LOOP
          RAISE NOTICE 'i = %', i;
      END LOOP;
      
      -- Reverse iteration
      FOR i IN REVERSE 5..1 LOOP
          RAISE NOTICE 'i = %', i;
      END LOOP;
      
      -- Custom step
      FOR i IN 1..10 BY 2 LOOP
          RAISE NOTICE 'i = %', i;  -- Output: 1, 3, 5, 7, 9
      END LOOP;
  END;

• FOR loop (query results): Iterates over each row returned by a query.

  DECLARE
      rec RECORD;
  BEGIN
      FOR rec IN SELECT id, name FROM employees ORDER BY id LOOP
          RAISE NOTICE 'ID: %, Name: %', rec.id, rec.name;
      END LOOP;
      
      -- Iterate over dynamic query results
      FOR rec IN EXECUTE 'SELECT * FROM employees WHERE salary > $1'
                 USING 50000
      LOOP
          RAISE NOTICE 'High earner: %', rec.name;
      END LOOP;
  END;

• FOREACH (iterate over arrays): Iterates over each element of an array.

  DECLARE
      arr integer[] := ARRAY[1, 2, 3, 4, 5];
      elem integer;
  BEGIN
      FOREACH elem IN ARRAY arr LOOP
          RAISE NOTICE 'Element: %', elem;
      END LOOP;
  END;

• EXIT and CONTINUE: EXIT terminates the current loop or a loop specified by a label. CONTINUE skips the current iteration and proceeds to the next.

  BEGIN
      <<outer_loop>>
      FOR i IN 1..10 LOOP
          FOR j IN 1..10 LOOP
              IF j = 5 THEN
                  CONTINUE;           -- Skip current inner iteration
              END IF;
              IF i * j > 30 THEN
                  EXIT outer_loop;    -- Exit outer loop
              END IF;
              RAISE NOTICE '% * % = %', i, j, i * j;
          END LOOP;
      END LOOP outer_loop;
  END;

Declare cursors

DECLARE
    -- Bound cursor: query is bound at compile time
    emp_cursor CURSOR FOR 
        SELECT id, name, salary FROM employees ORDER BY id;
    
    -- Unbound cursor
    ref_cursor refcursor;
    
    -- Parameterized cursor
    emp_cursor2 CURSOR (min_sal numeric) FOR
        SELECT * FROM employees WHERE salary >= min_sal;

Cursor operations

Cursors support the following operations:

• Open a cursor: Use different methods depending on the cursor type.

  DECLARE
      emp_cursor CURSOR FOR SELECT * FROM employees;
      ref_cursor refcursor;
  BEGIN
      -- Open a bound cursor
      OPEN emp_cursor;
      
      -- Open an unbound cursor
      OPEN ref_cursor FOR SELECT * FROM departments;
      
      -- Open a parameterized cursor
      OPEN emp_cursor2(50000);
      
      -- Open a dynamic cursor
      OPEN ref_cursor FOR EXECUTE 
          'SELECT * FROM ' || quote_ident(table_name);
  END;

• FETCH: Retrieve data: Read data from a cursor one row at a time.

  DECLARE
      emp_cursor CURSOR FOR SELECT id, name FROM employees;
      emp_id integer;
      emp_name text;
  BEGIN
      OPEN emp_cursor;
      
      LOOP
          FETCH emp_cursor INTO emp_id, emp_name;
          EXIT WHEN NOT FOUND;
          RAISE NOTICE 'ID: %, Name: %', emp_id, emp_name;
      END LOOP;
      
      CLOSE emp_cursor;
  END;

• FOR IN cursor: Automatically manages the cursor lifecycle. No manual open or close operations are required.

  DECLARE
      emp_cursor CURSOR FOR SELECT * FROM employees;
      rec RECORD;
  BEGIN
      FOR rec IN emp_cursor LOOP
          RAISE NOTICE 'Employee: %', rec.name;
      END LOOP;
      -- The cursor is opened and closed automatically
  END;

Cursors and performance

• Cursors fetch data row by row on demand without loading all results into memory at once.

• Cursors that remain open during a transaction consume database resources.

• In long-running transactions, unclosed cursors may prevent VACUUM from cleaning up expired data.

EXCEPTION blocks

Use the EXCEPTION clause within a BEGIN...END block to catch and handle exceptions.

CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric AS $$
DECLARE
    result numeric;
BEGIN
    BEGIN
        result := a / b;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'Division by zero, returning NULL';
            result := NULL;
        WHEN numeric_value_out_of_range THEN
            RAISE NOTICE 'Result out of range';
            result := NULL;
    END;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Common error conditions

Retrieve error information

Use the SQLERRM and SQLSTATE variables to obtain error details.

BEGIN
    INSERT INTO unique_table VALUES (duplicate_key);
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: % (SQLSTATE: %)', SQLERRM, SQLSTATE;
        IF SQLSTATE = '23505' THEN
            RAISE NOTICE 'Duplicate key error';
        END IF;
END;

Multiple exception handlers

BEGIN
    -- Business operations
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Record already exists';
    WHEN foreign_key_violation THEN
        RAISE NOTICE 'Referenced record not found';
    WHEN check_violation OR not_null_violation THEN
        RAISE NOTICE 'Data validation failed';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
        RAISE;  -- Re-raise the exception
END;

Subtransaction mechanism of EXCEPTION blocks

Each time a BEGIN block that contains an EXCEPTION clause is entered, PL/pgSQL creates a subtransaction.

Plan caching

PL/pgSQL automatically caches execution plans for SQL statements. A plan is generated and cached on the first execution, and subsequent calls reuse the cached plan.

CREATE FUNCTION get_employee(emp_id integer) RETURNS text AS $$
DECLARE
    emp_name text;
BEGIN
    -- This query plan is cached and reused for subsequent calls
    SELECT name INTO emp_name FROM employees WHERE id = emp_id;
    RETURN emp_name;
END;
$$ LANGUAGE plpgsql;

When parameter value distributions are skewed, the cached generic plan may not be optimal. In such cases, use EXECUTE to force plan regeneration on each call.

CREATE FUNCTION search_employees(status text) RETURNS SETOF employees AS $$
BEGIN
    -- status='active' accounts for 99% of data. The cached plan may not be optimal for status='inactive'.
    -- Use EXECUTE to re-plan on each call
    RETURN QUERY EXECUTE 
        'SELECT * FROM employees WHERE status = $1'
        USING status;
END;
$$ LANGUAGE plpgsql;

Minimize EXCEPTION block invocations

Functions that contain EXCEPTION blocks create a subtransaction on each call, which consumes vmem resources. Avoid calling such functions at high frequency in large-data scenarios.

You can check the current optimizer by running the following command:

SHOW optimizer;

Use EXPLAIN to verify the actual predicate execution order:

EXPLAIN SELECT * FROM large_table
WHERE cheap_condition
  AND expensive_plpgsql_function(column);

-- Bad: Call a function with EXCEPTION blocks row by row on millions of rows
SELECT * FROM million_rows WHERE plpgsql_func(col);

-- Good: Filter with low-cost conditions first to reduce function calls
SELECT * FROM large_table
WHERE cheap_condition
  AND expensive_plpgsql_function(column);

Memory management

RETURN NEXT and RETURN QUERY buffer result sets in memory. When the buffer exceeds the work_mem limit, it is written to disk. For large result sets, use RETURN QUERY.

-- Recommended: Use RETURN QUERY for large result sets
CREATE FUNCTION generate_rows() RETURNS SETOF integer AS $$
BEGIN
    RETURN QUERY SELECT generate_series(1, 1000000);
END;
$$ LANGUAGE plpgsql;

For scenarios that require row-by-row processing of large datasets, use cursors with batch processing to release resources periodically:

CREATE FUNCTION process_large_result() RETURNS void AS $$
DECLARE
    cur CURSOR FOR SELECT * FROM large_table;
    rec RECORD;
    batch_size integer := 1000;
    counter integer := 0;
BEGIN
    FOR rec IN cur LOOP
        -- Process a single row
        PERFORM process_row(rec.id);
        
        counter := counter + 1;
        IF counter % batch_size = 0 THEN
            -- Release resources periodically
            RAISE NOTICE 'Processed % rows', counter;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Batch operations

The performance of PL/pgSQL loops for row-by-row processing is significantly lower than SQL set-based operations. Use native SQL set-based operations instead of loops whenever possible.

-- Bad: Update rows one at a time (slow)
CREATE FUNCTION update_salaries_slow() RETURNS void AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id, salary FROM employees LOOP
        UPDATE employees SET salary = rec.salary * 1.1 WHERE id = rec.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Good: Set-based operation (fast)
CREATE FUNCTION update_salaries_fast() RETURNS void AS $$
BEGIN
    UPDATE employees SET salary = salary * 1.1;
END;
$$ LANGUAGE plpgsql;

-- Or use SQL directly without PL/pgSQL
UPDATE employees SET salary = salary * 1.1;

Use PERFORM to execute batch DML operations:

BEGIN
    -- Batch delete
    PERFORM 1 FROM delete_old_records();
    
    -- Batch insert
    PERFORM insert_batch_data(source_table, target_table);
END;

Alternatives to avoid row-by-row processing:

-- Bad: Row-by-row insertion
FOR i IN 1..1000 LOOP
    INSERT INTO target VALUES (i, 'value' || i);
END LOOP;

-- Good: Batch insertion
INSERT INTO target 
SELECT i, 'value' || i FROM generate_series(1, 1000) i;

-- Or use COPY
COPY target FROM '/path/to/data.csv';

Debug with RAISE NOTICE

CREATE FUNCTION debug_example(input integer) RETURNS integer AS $$
DECLARE
    step1_result integer;
    step2_result integer;
BEGIN
    RAISE NOTICE 'Function called with input: %', input;
    
    step1_result := input * 2;
    RAISE NOTICE 'After step 1: %', step1_result;
    
    step2_result := step1_result + 10;
    RAISE NOTICE 'After step 2: %', step2_result;
    
    RETURN step2_result;
END;
$$ LANGUAGE plpgsql;

Handle variable name conflicts

When a PL/pgSQL variable has the same name as a SQL column, use the plpgsql.variable_conflict parameter to control priority.

SET plpgsql.variable_conflict = 'error';         -- Raise an error (default)
SET plpgsql.variable_conflict = 'use_variable';  -- Prioritize the variable
SET plpgsql.variable_conflict = 'use_column';    -- Prioritize the column

Configure log levels

SET log_min_messages = 'debug1';       -- Set server log level
SET client_min_messages = 'notice';    -- Set client message level