Stored procedures - Hologres: Central de Documentação do Alibaba Cloud

Limitações

O Hologres oferece suporte a stored procedures com sintaxe PL/pgSQL a partir da versão V3.0. Para obter mais informações sobre a sintaxe PL/pgSQL, consulte SQL Procedural Language.
Em stored procedures do Hologres, é possível executar várias instruções DDL ou várias instruções DML em uma única transação. No entanto, não é permitido combinar instruções DDL e DML na mesma transação. Para mais detalhes, consulte Transações.
Stored procedures não aceitam valores de retorno nem funcionam como funções definidas pelo usuário (UDFs).
Stored procedures não oferecem suporte à instrução RETURN QUERY para retornar conjuntos de resultados. Para devolver dados, use uma view ou uma tabela temporária.
Stored procedures não oferecem suporte à operação CURSOR.
Não é possível definir um stored procedure dentro de outro.
A instrução EXECUTE ... INTO não permite atribuir resultados de SQL dinâmico a uma variável. Use a instrução SELECT ... INTO em seu lugar.

Permissões

Para executar a instrução CREATE PROCEDURE, é necessária a permissão CREATE no banco de dados, a mesma exigida para criar uma tabela. Consulte CREATE PROCEDURE para mais informações.
Executar a instrução CREATE OR REPLACE exige a permissão CREATE no banco de dados e a propriedade do stored procedure a ser substituído. Consulte CREATE PROCEDURE para mais detalhes.
Chamar um stored procedure requer a permissão EXECUTE sobre ele. Para mais informações, consulte CALL.

Referência de comandos

O Hologres oferece suporte à sintaxe de stored procedure compatível com PostgreSQL. A seção a seguir detalha essa sintaxe.

Criar um stored procedure

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

Parâmetro	Descrição
procedure_name	Nome do stored procedure.
argname	Nome de um argumento. Este parâmetro é opcional e depende do design do stored procedure.
argtype	Tipo de dado do argumento.
definition	Implementação específica do stored procedure. Pode ser uma instrução SQL ou um bloco de código.

Para mais detalhes sobre os parâmetros, consulte CREATE PROCEDURE.

Alterar um stored procedure

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

Parâmetro	Descrição
new_owner	Novo proprietário.
CURRENT_USER	Usuário atual.
SESSION_USER	Usuário da sessão.

Para obter mais informações sobre os parâmetros, consulte ALTER PROCEDURE.

Excluir um stored procedure

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

Para mais detalhes sobre os parâmetros, consulte DROP PROCEDURE.

Chamar um stored procedure

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

Parâmetro	Descrição
argument	Argumento do stored procedure. Este parâmetro é opcional e depende do design do procedimento.

Para mais informações sobre os parâmetros, consulte CALL.

Exemplos

Exemplo 1: Stored procedure com transação DDL de múltiplas instruções

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

Chame o stored procedure. As tabelas a1 e a2 são criadas, mas as tabelas a3 e a4 não.
```
CALL procedure_1();
```

Exemplo 2: Stored procedure com transação DML de múltiplas instruções

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

Chame os stored procedures.

Execute procedure_2. A transação sofre rollback e nenhum dado é gravado.

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

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

Execute procedure_3. Os dados são gravados com sucesso.

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

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

Exemplo 3: Stored procedure combinando instruções DDL e DML

Crie um stored procedure. Como o Hologres não permite misturar instruções DDL e DML na mesma transação, confirme as operações DDL e DML separadamente dentro do 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;
$$;

Chame o stored procedure. A tabela é criada e os dados são gravados corretamente.

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

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

Exemplo 4: Stored procedure demonstrando recursos comuns, como definição de parâmetros de entrada, variáveis intermediárias, loops, condições IF e tratamento de EXCEPTION.

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

Chame o stored procedure. O valor 1 é gravado na tabela a3, nenhum outro dado é inserido e todos os notices relacionados são impressos.

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

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

Exemplo 5: Uso de expressão CASE WHEN para calcular dinamicamente e reutilizar uma variável.

Crie a tabela de destino e o stored procedure. O procedimento declara uma variável com DECLARE, calcula dinamicamente um parâmetro de tempo com uma expressão CASE WHEN e reutiliza a variável em várias instruções 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;
$$;

Chame o stored procedure e verifique o resultado. Os valores de end_time dos dois registros são idênticos, pois derivam do mesmo 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;

Gerenciar stored procedures

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

Visualize a definição de um stored procedure.

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

FAQ

O Hologres é um sistema distribuído que precisa sincronizar metadados entre seus nós de frontend (FEs) em tempo real durante operações DDL. Se a sincronização de metadados não for concluída, a operação DDL poderá falhar. Embora o Hologres geralmente tente repetir automaticamente operações DDL com falha, esse mecanismo não funciona dentro de stored procedures. Caso esse problema ocorra em um stored procedure, o sistema retornará um erro HG_PLPGSQL_NEED_RETRY.

Para evitar erros em tabelas sujeitas a alterações DDL frequentes, implemente uma lógica de nova tentativa manual em seus stored procedures. O código abaixo fornece um exemplo:

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