Stored procedures-Hologres(Hologres)-阿里云帮助中心

Limitations

Hologres supports stored procedures that use the PL/pgSQL syntax starting from V3.0. For more information about the PL/pgSQL syntax, see SQL Procedural Language.
In Hologres stored procedures, you can run multiple DDL statements in a single transaction or multiple DML statements in a single transaction. However, you cannot run DDL and DML statements in the same transaction. For more information, see Transactions.
Stored procedures do not support return values and cannot be used as user-defined functions (UDFs).
Stored procedures do not support the RETURN QUERY statement to return result sets. To return data, use a view or a temporary table.
Stored procedures do not support the CURSOR operation.
You cannot define a stored procedure within another stored procedure.
Stored procedures do not support the EXECUTE ... INTO statement to assign dynamic SQL results to a variable. Use the SELECT ... INTO statement instead.

Permissions

To run the CREATE PROCEDURE statement, you must have the CREATE permission on the database. This is the same permission required to create a table. For more information, see CREATE PROCEDURE.
To run the CREATE OR REPLACE statement, you must have the CREATE permission on the database and be the owner of the stored procedure that you want to replace. For more information, see CREATE PROCEDURE.
To call a stored procedure, you must have the EXECUTE permission on the stored procedure. For more information, see CALL.

Command reference

Hologres supports stored procedure syntax that is compatible with PostgreSQL. The following section describes the syntax.

Create a stored procedure

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

Parameter	Description
procedure_name	The name of the stored procedure.
argname	The name of an argument. This parameter is optional and depends on the stored procedure design.
argtype	The data type of the argument.
definition	The specific implementation of the stored procedure. This can be an SQL statement or a code block.

For more information about the parameters, see CREATE PROCEDURE.

Alter a stored procedure

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

Parameter	Description
new_owner	The new owner.
CURRENT_USER	The current user.
SESSION_USER	The session user.

For more information about the parameters, see ALTER PROCEDURE.

Drop a stored procedure

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]);

For more information about the parameters, see DROP PROCEDURE.

Call a stored procedure

CALL <procedure_name> ([<argument>]);

Parameter	Description
argument	An argument for the stored procedure. This parameter is optional and depends on the procedure's design.

For more information about the parameters, see CALL.

Examples

Example 1: Stored procedure with a multi-statement DDL transaction

Create a stored procedure.

CREATE OR REPLACE PROCEDURE procedure_1()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    --- TXN1 --- 
    CREATE TABLE a1(key int);
    CREATE TABLE a2(key int);
    COMMIT; 

    --- TXN2 ---
    CREATE TABLE a3(key int);
    CREATE TABLE a4(key int);
    ROLLBACK;
END; 
$$;

Call the stored procedure. Tables a1 and a2 are created, but tables a3 and a4 are not created.
```
CALL procedure_1();
```

Example 2: Stored procedure with a multi-statement DML transaction

Create the stored procedures.

CREATE OR REPLACE PROCEDURE procedure_2()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
    ROLLBACK;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure_3()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
END;
$$;

Call the stored procedures.

Call procedure_2. The transaction is rolled back, and no data is written.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_2();

Call procedure_3. The data is written successfully.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_3();

Example 3: Stored procedure with both DDL and DML statements

Create a stored procedure. Because Hologres does not support mixed DDL and DML statements in the same transaction, you must commit DDL and DML operations separately within the stored procedure.

CREATE OR REPLACE PROCEDURE procedure_4()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    COMMIT;	
    CREATE TABLE bb(key int);
    COMMIT;	
    INSERT INTO a1 VALUES(2);
    INSERT INTO bb VALUES(1);    
    COMMIT;	
END;
$$;

Call the stored procedure. The table is created and data is written successfully.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_4();

Example 4: A stored procedure demonstrating common features, such as defining input parameters, intermediate variables, loops, IF conditions, and EXCEPTION handling.

Create a stored procedure.

CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- Define an intermediate variable.
DECLARE
sql1 text;
BEGIN
    -- Insert a row of data into the table specified by the input parameter.
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- Create table a3.
    CREATE TABLE a3(key int);
    COMMIT;

    -- Use the intermediate variable to insert a row into table a3.
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- Define a FOR loop.
    FOR i IN 1..10 LOOP
        BEGIN
            -- Because i=1 already exists in the table, only a notice is raised.
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- Other numbers do not exist in the table. The system attempts to insert them,
            -- raises an EXCEPTION, and then commits.
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- For the raised EXCEPTION, raise a notice.
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

Call the stored procedure. The value 1 is written to table a3, no other data is written, and all related notices are printed.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_5('a1');

Example 5: Using a CASE WHEN expression to dynamically calculate and reuse a variable.

Create the destination table and the stored procedure. The procedure declares a variable with DECLARE, dynamically calculates a time parameter with a CASE WHEN expression, and reuses the variable in multiple INSERT statements.

-- Create the destination table.
CREATE TABLE test_dynamic_param (
    event_name TEXT,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ
);

-- Create a stored procedure that uses a CASE WHEN expression to dynamically calculate a time parameter.
CREATE OR REPLACE PROCEDURE procedure_dyn_time()
LANGUAGE 'plpgsql'
AS $$
DECLARE
    new_end_time TIMESTAMPTZ;
    base_start_time TIMESTAMPTZ := '2024-01-01 00:00:00+08'::TIMESTAMPTZ;
BEGIN
    -- Use CASE WHEN to dynamically calculate end_time.
    new_end_time := CASE
        WHEN NOW() > base_start_time + INTERVAL '5 min'
        THEN base_start_time + INTERVAL '1 hour'
        ELSE base_start_time + INTERVAL '5 min'
    END;

    -- Reuse the variable in multiple INSERT statements.
    INSERT INTO test_dynamic_param VALUES('event1', base_start_time, new_end_time);
    INSERT INTO test_dynamic_param VALUES('event2', base_start_time, new_end_time);
END;
$$;

Call the stored procedure and verify the result. The end_time values for the two records are identical as they are derived from the same CASE WHEN calculation.

-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

-- Call the stored procedure.
CALL procedure_dyn_time();

-- Verification: The end_time values for the two records are identical.
SELECT * FROM test_dynamic_param;

Manage stored procedures

View stored procedures.

SELECT
    p.proname AS procedure_name,
    pg_get_function_identity_arguments(p.oid) AS argument_types,
    REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    d.description AS description
FROM
    pg_proc p
    INNER JOIN pg_namespace n ON p.pronamespace = n.oid
    INNER JOIN pg_roles r ON p.proowner = r.oid
    LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE
    r.rolname != 'holo_admin'
    AND p.prokind = 'p'
ORDER BY
    n.nspname,
    p.proname;

View the definition of a stored procedure.

SELECT pg_get_functiondef('<procedure_name>'::regproc);

FAQ

Hologres is a distributed system that must synchronize metadata across its frontend nodes (FEs) in real time during DDL operations. If the metadata synchronization is not complete, the DDL operation might fail. While Hologres typically retries failed DDL operations automatically, this mechanism is not supported within stored procedures. If this issue occurs in a stored procedure, the system returns an HG_PLPGSQL_NEED_RETRY error.

To prevent errors on tables that undergo frequent DDL changes, implement manual retry logic within your stored procedures. The following code provides an example:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Try to run the DDL statement. If the statement is successful, exit the loop.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- If an HG_PLPGSQL_NEED_RETRY error occurs, print a notice and retry the operation.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;