Stored procedures - Hologres - Centro de documentación de Alibaba Cloud

Un procedimiento almacenado es un conjunto de comandos SQL precompilados que se almacenan en una base de datos para invocarlos de forma repetida. Este tema describe cómo usar procedimientos almacenados en Hologres.

Límites

A partir de la versión V3.0, Hologres admite procedimientos almacenados con sintaxis PL/pgSQL. Para más información sobre esta sintaxis, consulte Lenguaje procedimental SQL.
En los procedimientos almacenados de Hologres, puede ejecutar varios comandos DDL o varios comandos DML dentro de una misma transacción. Sin embargo, no es posible combinar comandos DDL y DML en la misma transacción. Para más información, consulte Transactions.
Los procedimientos almacenados no admiten valores de retorno ni funcionan como funciones definidas por el usuario (UDF).
Los procedimientos almacenados no admiten el comando RETURN QUERY para devolver conjuntos de resultados. Si necesita devolver datos, utilice una vista o una tabla temporal.
Los procedimientos almacenados no admiten la operación CURSOR.
No es posible definir un procedimiento almacenado dentro de otro.
Los procedimientos almacenados no admiten el comando EXECUTE ... INTO para asignar resultados de SQL dinámico a una variable. En su lugar, utilice el comando SELECT ... INTO.

Permisos

Para ejecutar el comando CREATE PROCEDURE, debe contar con el permiso CREATE sobre la base de datos. Se trata del mismo permiso necesario para crear una tabla. Para más información, consulte CREATE PROCEDURE.
Para ejecutar el comando CREATE OR REPLACE, requiere el permiso CREATE sobre la base de datos y debe ser el propietario del procedimiento almacenado que desea reemplazar. Para más información, consulte CREATE PROCEDURE.
Para invocar un procedimiento almacenado, necesita el permiso EXECUTE sobre dicho procedimiento. Para más información, consulte CALL.

Referencia de comandos

Hologres admite una sintaxis de procedimientos almacenados compatible con PostgreSQL. A continuación se describe dicha sintaxis.

Crear un procedimiento almacenado

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

Parámetro	Descripción
procedure_name	Nombre del procedimiento almacenado.
argname	Nombre de un argumento. Este parámetro es opcional y depende del diseño del procedimiento almacenado.
argtype	Tipo de dato del argumento.
definition	Implementación específica del procedimiento almacenado. Puede tratarse de un comando SQL o de un bloque de código.

Para más información sobre los parámetros, consulte CREATE PROCEDURE.

Modificar un procedimiento almacenado

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

Parámetro	Descripción
new_owner	Nuevo propietario.
CURRENT_USER	Usuario actual.
SESSION_USER	Usuario de la sesión.

Para más información sobre los parámetros, consulte ALTER PROCEDURE.

Eliminar un procedimiento almacenado

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

Para más información sobre los parámetros, consulte DROP PROCEDURE.

Invocar un procedimiento almacenado

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

Parámetro	Descripción
argument	Argumento del procedimiento almacenado. Este parámetro es opcional y depende del diseño del procedimiento.

Para más información sobre los parámetros, consulte CALL.

Ejemplos

Ejemplo 1: procedimiento almacenado con una transacción DDL de múltiples comandos.

Cree un procedimiento almacenado.

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; 
$$;

Invoque el procedimiento almacenado. Las tablas a1 y a2 se crean correctamente, mientras que las tablas a3 y a4 no se crean.
```
CALL procedure_1();
```

Ejemplo 2: procedimiento almacenado con una transacción DML de múltiples comandos.

Cree los procedimientos almacenados.

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;
$$;

Invoque los procedimientos almacenados.

Invoque procedure_2. La transacción se revierte y no se escriben datos.

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

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

Invoque procedure_3. Los datos se escriben correctamente.

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

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

Ejemplo 3: procedimiento almacenado que combina comandos DDL y DML.

Cree un procedimiento almacenado. Como Hologres no admite mezclar comandos DDL y DML en la misma transacción, debe confirmar las operaciones DDL y DML por separado dentro del procedimiento.

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;
$$;

Invoque el procedimiento almacenado. La tabla se crea y los datos se escriben correctamente.

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

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

Ejemplo 4: procedimiento almacenado que muestra funciones comunes, como la definición de parámetros de entrada, variables intermedias, bucles, condiciones IF y manejo de EXCEPTION.

Cree un procedimiento almacenado.

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;
$$;

Invoque el procedimiento almacenado. El valor 1 se escribe en la tabla a3, no se escribe ningún otro dato y se muestran todos los avisos relacionados.
```
-- Enable the DML transaction feature.
SET hg_experimental_enable_transaction = ON;

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

Ejemplo 5: uso de una expresión CASE WHEN para calcular de forma dinámica y reutilizar una variable.

Cree la tabla de destino y el procedimiento almacenado. El procedimiento declara una variable con DECLARE, calcula de forma dinámica un parámetro de tiempo mediante una expresión CASE WHEN y reutiliza la variable en varios comandos INSERT.

-- 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;
$$;

Invoque el procedimiento almacenado y compruebe el resultado. Los valores de end_time de ambos registros son idénticos, ya que provienen del mismo cálculo CASE WHEN.

-- 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;

Gestionar procedimientos almacenados

Consulte los procedimientos almacenados.

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;

Consulte la definición de un procedimiento almacenado.

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

Preguntas frecuentes

Hologres es un sistema distribuido que sincroniza metadatos en tiempo real entre sus nodos frontend (FE) durante las operaciones DDL. Si la sincronización de metadatos no concluye, la operación DDL podría fallar. Aunque Hologres suele reintentar automáticamente las operaciones DDL con errores, este mecanismo no es compatible dentro de los procedimientos almacenados. Cuando ocurre este problema en un procedimiento almacenado, el sistema devuelve un error HG_PLPGSQL_NEED_RETRY.

Para evitar errores en tablas con cambios DDL frecuentes, implemente una lógica de reintento manual dentro de sus procedimientos almacenados. El siguiente código muestra un ejemplo:

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;
$$;