Hologres:Stored procedures

Create a stored procedure

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

For the full parameter reference, see SQL-CREATE PROCEDURE.

Modify a stored procedure

Use ALTER PROCEDURE to transfer ownership of a stored procedure.

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

For the full parameter reference, see SQL-ALTER PROCEDURE.

Drop a stored procedure

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

For the full parameter reference, see SQL-DROP PROCEDURE.

Call a stored procedure

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

For the full parameter reference, see SQL-CALL.

DDL transactions

The following procedure runs two DDL transactions. TXN1 commits, creating tables a1 and a2. TXN2 rolls back, so tables a3 and a4 are not created.

1. Create the 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;
   $$;

2. Call the procedure.

   CALL procedure_1();

   Result: Tables a1 and a2 are created. Tables a3 and a4 are not created.

DML transactions

DML transactions inside stored procedures require the hg_experimental_enable_transaction parameter. Enable it before calling any procedure that uses DML transactions.

The following two procedures show the effect of ROLLBACK versus implicit commit.

1. Create the procedures.

   -- procedure_2: inserts and then rolls back—no data is written.
   CREATE OR REPLACE PROCEDURE procedure_2()
   LANGUAGE 'plpgsql'
   AS $$
   BEGIN
       INSERT INTO a1 VALUES(1);
       INSERT INTO a2 VALUES(2);
       ROLLBACK;
   END;
   $$;
   
   -- procedure_3: inserts without explicit ROLLBACK—data is written.
   CREATE OR REPLACE PROCEDURE procedure_3()
   LANGUAGE 'plpgsql'
   AS $$
   BEGIN
       INSERT INTO a1 VALUES(1);
       INSERT INTO a2 VALUES(2);
   END;
   $$;

2. Call the procedures.

   -- Enable DML transactions.
   SET hg_experimental_enable_transaction = ON;
   
   -- Run procedure_2. ROLLBACK prevents data from being written.
   CALL procedure_2();
   
   -- Run procedure_3. Data is written successfully.
   CALL procedure_3();

Mixed DDL and DML in one procedure

Because Hologres does not support mixed DDL/DML transactions, commit each block separately. The following procedure inserts data, creates a table, and inserts more data—each in its own transaction.

1. Create the 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;
   $$;

2. Call the procedure.

   -- Enable DML transactions.
   SET hg_experimental_enable_transaction = ON;
   
   -- Run the procedure. The table is created and data is written.
   CALL procedure_4();

Input parameters, variables, loops, and exception handling

The following procedure demonstrates common PL/pgSQL constructs: input parameters, intermediate variables, a FOR loop, IF conditions, and EXCEPTION handling.

1. Create the procedure.

   CREATE OR REPLACE PROCEDURE procedure_5(input text)
   LANGUAGE 'plpgsql'
   AS $$
   -- Declare an intermediate variable.
   DECLARE
   sql1 text;
   BEGIN
       -- Write a row to 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 variable to write a row to table a3.
       sql1 = 'insert into a3 values(1);';
       EXECUTE sql1;
   
       -- Loop from 1 to 10.
       FOR i IN 1..10 LOOP
           BEGIN
               -- i=1 already exists; print a notice and skip.
               IF i IN (SELECT KEY FROM a3) THEN
                   RAISE NOTICE 'Data already exists.';
               -- For other values, attempt to insert, then raise an exception.
               ELSE
                   INSERT INTO a3 VALUES(i);
                   RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                   COMMIT;
               END IF;
           -- Catch any exception and log it.
           EXCEPTION
               WHEN OTHERS THEN
                   RAISE NOTICE 'Catch error.';
           END;
       END LOOP;
   
   END;
   $$;

2. Call the procedure.

   -- Enable DML transactions.
   SET hg_experimental_enable_transaction = ON;
   
   -- Run the procedure with 'a1' as the input table.
   CALL procedure_5('a1');

   Result: The value 1 is written to table a3. No other values are written. All related log entries are printed.

Handle DDL retry errors

When a DDL statement inside a stored procedure encounters the HG_PLPGSQL_NEED_RETRY error, the procedure does not retry automatically. For tables with frequent DDL changes, wrap the DDL statement in a WHILE TRUE LOOP with exception handling to retry automatically until the statement succeeds.

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Attempt the DDL statement. Exit the loop on success.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- On HG_PLPGSQL_NEED_RETRY, log and retry.
            WHEN HG_PLPGSQL_NEED_RETRY THEN
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;

List all stored procedures

The following query returns all stored procedures in the database, excluding system procedures owned by holo_admin.

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 a stored procedure definition

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