浅谈DWS函数出参方式

浅谈,dws,函数,方式 · 浏览次数 : 29

小编点评

## Summary This document discusses the issue with the `PERFORM` syntax in DWS PL/pgSQL functions and stored procedures. The main purpose of `PERFORM` is to execute a statement without storing its result, which can be useful for certain state-checking scenarios. However, due to the way `PERFORM` is used, it often leads to incorrect variable assignments. Here's a breakdown of the issue: * `inner` function call passes its arguments to variables `a` and `b`. * `a` and `b` are subsequently assigned the values of `i_a` and `i_b`, respectively. * `a` and `b` are used in the `RAISE INFO` statement without being properly assigned. This leads to misleading results, as the actual values of `a` and `b` are not captured and used. **Possible solutions:** * Use alternative methods for passing arguments, such as `IN` or dynamic queries. * Use `INTO` assignment with `SELECT` to capture the result of the inner function. **Example:** ```sql -- Method 1: Using IN CREATE OR REPLACE FUNCTION public.test1(IN i_a int, IN i_b int) RETURNS void LANGUAGE plpgsql NOT FENCED NOT SHIPPABLEAS $function$ DECLARE a int; b int;BEGIN public.inner(i_a, i_b, a, b); RAISE info 'a = %, b = %', a, b;END$function; -- Method 2: Using Dynamic Query CREATE OR REPLACE FUNCTION public.test2(IN i_a int, IN i_b int) RETURNS void LANGUAGE plpgsql NOT FENCED NOT SHIPPABLEAS $function$ DECLARE a int; b int;BEGIN EXECUTE IMMEDIATE 'SELECT * from public.inner(:1, :2)' UNSING INTO a, b USING IN i_a, i_b; RAISE INFO 'a = %, b = %', a, b;END$function; ``` **Conclusion:** Understanding the `PERFORM` syntax and its limitations is crucial for working with DWS PL/pgSQL functions and stored procedures. By implementing the suggested solutions, you can achieve accurate variable assignments and improve your code's clarity and efficiency.

正文

摘要：DWS的PL/pgSQL函数/存储过程中有一个特殊的语法PERFORM语法，用于执行语句但是丢弃执行结果的场景，常用于一些状态判断的场景。

本文分享自华为云社区《GassDB(DWS)功能 -- 函数出参 #【玩转PB级数仓GaussDB(DWS)】》，作者：譡里个檔。

DWS的PL/pgSQL函数/存储过程中有一个特殊的语法PERFORM语法，用于执行语句但是丢弃执行结果的场景，常用于一些状态判断的场景。但是客户往往会不当使用PERFORM语法，导致业务逻辑出错，最常见的就是使用PERFORM执行带有出参的函数。

已知函数inner定义如下

CREATE OR REPLACE FUNCTION public.inner(
    IN a1 integer, 
    IN b1 integer, 
    OUT a integer, 
    OUT b integer
)
 RETURNS record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ 
DECLARE
BEGIN
 a := a1;
 b := b1;
END$function$
;

函数f_outer定义如下，函数体中调用函数inner，把函数的出参赋值给变量a, b

CREATE OR REPLACE FUNCTION public.f_outer(IN i_a int, IN i_b int)
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ 
DECLARE
 a int;
    b int;
BEGIN
    PERFORM public.inner(i_a, i_b, a, b);
    RAISE INFO 'a = %, b = %', a, b;
END$function$
;

但是实际执行的时候发现函数inner的出参没有正确赋值(预期值为a = 1 b = 11)。

postgres=# CALL f_outer(1, 11);
INFO:  a = <NULL>, b = <NULL>
SQLSTATE: 00000
 f_outer
---------
(1 row)
Time: 1.086 ms

出现这种问题的原因是PERFORM语法会执行SQL语句，但是会抛弃执行结果，导致函数出参没有赋值

DWS中常用的带出函数出参的方式有以下三种：

方式1：函数出参的方式赋值

CREATE OR REPLACE FUNCTION public.test1(IN i_a int, IN i_b int)
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ 
DECLARE
 a int;
    b int;
BEGIN
 public.inner(i_a, i_b, a, b);
    RAISE info 'a = %, b = %', a, b;
END$function$
;

方式2：动态查询语句方式赋值

CREATE OR REPLACE FUNCTION public.test2(IN i_a int, IN i_b int)
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ 
DECLARE
 a int;
    b int;
BEGIN
 EXECUTE IMMEDIATE 'SELECT * from public.inner(:1, :2)' UNSING INTO a, b USING IN i_a, i_b;
    RAISE INFO 'a = %, b = %', a, b;
END$function$
;

方式3：SELECT .. INTO赋值

CREATE OR REPLACE FUNCTION public.test3(IN i_a int, IN i_b int)
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$ 
DECLARE
 a int := 0;
    b int := 0;
BEGIN
 SELECT * INTO a, b FROM public.inner(i_a, i_b);
    RAISE INFO 'a = %, b = %', a, b;
END$function$
;

点击关注，第一时间了解华为云新鲜技术~