526互联

lightdb plpgsql函数in/inout参数示例

发布时间 2023-07-15 17:52:49作者: lightdb

　　lightdb支持oracle pl/sql以及开源postgresql Plpgsql两种过程性（增强）语言。本文讲解pgpgsql函数出参的典型用法及限制。

　　注：匿名块实际上走的是plorasql，而非plpgsql，即使调用的是plpgsql过程。

　　本文我们假设对于函数、存储过程的调用是进行逻辑处理，而不是返回结果集或游标，这通常是两种上下文场景。

1、如果有多个出参，则不能带return预定义类型或必须return record

zjh@postgres=# CREATE OR REPLACE FUNCTION public.f_square(inout v_a int, inout v_str varchar) returns int
    LANGUAGE 'plpgsql'
AS $BODY$
declare  
    lv_result int; 
begin    
    v_a := v_a * v_a;
v_str := v_str || '..ret';
end                  
$BODY$;
CREATE FUNCTION
zjh@postgres=# select f_square(10,'a');                                                                 
ERROR:  invalid input syntax for type integer: "(100,a..ret)"
CONTEXT:  PL/pgSQL function f_square(integer,character varying) while casting return value to function's return type
除了事务外，该限制和oracle存在明显的不兼容性。
zjh@postgres=# CREATE OR REPLACE FUNCTION public.f_square(inout v_a int) returns int
    LANGUAGE 'plpgsql'
AS $BODY$
declare  
    lv_result int; 
begin    
    v_a := v_a * v_a;
-- v_str := v_str || '..ret';
end                  
$BODY$;
CREATE FUNCTION
zjh@postgres=# 
zjh@postgres=# 
zjh@postgres=# select f_square(10);
 f_square 
----------
      100
(1 row)
zjh@postgres=# CREATE OR REPLACE FUNCTION public.f_square(inout v_a int, inout v_str varchar) returns record
    LANGUAGE 'plpgsql'
AS $BODY$
declare  
    lv_result int; 
begin    
    v_a := v_a * v_a;
v_str := v_str || '..ret';
end                  
$BODY$;
CREATE FUNCTION
zjh@postgres=# select f_square(10,'a');
   f_square   
--------------
 (100,a..ret)
(1 row)

2、plpgsql匿名块或过程中使用perform调用函数未修改出参值

zjh@postgres=# CREATE OR REPLACE FUNCTION public.f_square(inout v_a int, inout v_str varchar) returns record
    LANGUAGE 'plpgsql'
AS $BODY$
declare  
    lv_result int; 
begin    
    v_a := v_a * v_a;
    v_str := v_str || '..ret';
    raise notice 'v_str=%',v_str;
end                  
$BODY$;
CREATE FUNCTION
zjh@postgres=# declare                                                                                      
  v_a int :=10;
  v_str varchar(100) := 'b';
begin
  perform f_square(v_a,v_str);
  raise notice 'v_a=%,v_str=%',v_a,v_str;
end;
/
NOTICE:  v_str=b..ret
NOTICE:  v_a=10,v_str=b
DO
zjh@postgres=# declare
  v_a int :=10;
  v_str varchar(100) := 'b';
begin
  select * into v_a,v_str from f_square(v_a,v_str);
  raise notice 'v_a=%,v_str=%',v_a,v_str;
end;
/
NOTICE:  v_str=b..ret
NOTICE:  v_a=100,v_str=b..ret
DO

　　为什么通过perform调用不生效，直接select 函数(v1,v2)，select into o1,o2 from 函数(v1,v2)能返回呢？因为本质上in/out/inout都是传值处理，所有的出参事实上都是通过return scalar或return record实现。exec_stmt_execsql处理完了into才丢弃SPI_tuptable，exec_stmt_perform则立刻丢弃。

　　postgresql函数OUT和INOUT使用方法 https://blog.csdn.net/llj318/article/details/122387617

实现oracle的v_ret := fnc(in a, out b,inout c);调用兼容支持

declare                                                                                      
  v_a int :=10;
  v_str varchar(100) := 'b';
  v_ret int;
begin
  v_ret := f_square(v_a,v_str); -- 因为pg不支持带出参返回非record的函数定义，所以自然也就不支持oracle的这种调用。
  raise notice 'v_a=%,v_str=%',v_a,v_str;
end;

其实只要实现PLpgSQL_execstate.retval即可（因为record存储在SPITupleTable *eval_tuptable成员中，所以不存在破坏）。
typedef struct PLpgSQL_execstate
{
    PLpgSQL_function *func;        /* function being executed */

    TriggerData *trigdata;        /* if regular trigger, data about firing */
    EventTriggerData *evtrigdata;    /* if event trigger, data about firing */

    Datum        retval;
    bool        retisnull;
    Oid            rettype;        /* type of current retval */
    /* temporary state for results from evaluation of query or expr */
    SPITupleTable *eval_tuptable;
    uint64        eval_processed;

lightdb将在23.3版本支持该兼容性。

调用存储过程获取出参

zjh@postgres=# create or replace procedure proce_inouttest(in nD1 bigint, inout szD varchar, out nD2 integer)
zjh@postgres-# as 
zjh@postgres$# $$
zjh@postgres$# begin
zjh@postgres$#     nD1:=99;
zjh@postgres$#     szD:='qaz';
zjh@postgres$#     nD2:=88;
zjh@postgres$# end;
zjh@postgres$# $$
zjh@postgres-# language plpgsql;

zjh@postgres=# call proce_inouttest(1,'3',1);
 szd | nd2 
-----+-----
 qaz |  88
(1 row)

CALL executes a procedure.

If the procedure has any output parameters, then a result row will be returned, containing the values of those parameters.

zjh@postgres=# DO $$
zjh@postgres$# DECLARE myvar int := 5;
zjh@postgres$# BEGIN
zjh@postgres$#   CALL triple(myvar);
zjh@postgres$#   RAISE NOTICE 'myvar = %', myvar;  -- prints 15
zjh@postgres$# END;
zjh@postgres$# $$;
NOTICE:  myvar = 15

　　在plpgsql中，存储过程是支持出参赋值的。参见http://www.light-pg.com/docs/lightdb/13.8-22.3/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE。在请求返回的时候，OUT重新赋值回去的。这一点存储过程和函数的行为不一样。

libpq调用存储过程

#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
 
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
 
 
static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}
 
void test_call_sp()
{
    const char *conninfo;
    PGconn       *conn;
    PGresult   *res;
    const char *paramValues[3];
    int            paramLengths[3];
    int            paramFormats[3];
    uint32_t    binaryIntVal;
    uint64_t    bigbinaryIntVal;
    int            nFields;
    int            i,
                j;
 
 
    conninfo = "postgresql:///postgres?host=localhost&port=18888";
    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);
 
    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }
 
    /* Convert integer value "2" to network byte order */
    binaryIntVal = htonl((uint32_t) 2);
    bigbinaryIntVal = htonl((uint64_t) 2);
 
    /* Set up parameter arrays for PQexecParams */
    paramValues[0] = (char *) &bigbinaryIntVal;
    paramLengths[0] = sizeof(bigbinaryIntVal);
    paramFormats[0] = 1;        /* binary */
 
    paramValues[1] = "2";
    paramLengths[1] = 1;
    paramFormats[1] = 0;        /* test */
 
    paramValues[2] = (char *) &binaryIntVal;
    paramLengths[2] = sizeof(binaryIntVal);
    paramFormats[2] = 1;        /* binary */
 
    res = PQexecParams(conn,
                       "call proce_inouttest($1,$2,$3)",
                       3,        /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       paramLengths,
                       paramFormats,
                       0);        /* ask for text results */
 
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");
 
    /* next, print out the instances */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }
 
    PQclear(res);
 
    /* close the connection to the database and cleanup */
    PQfinish(conn);
 
    return 0;
}
create or replace procedure proce_inouttest(in nD1 bigint, inout szD varchar, out nD2 integer)
as 
$$
begin
    nD1:=99;
    szD:='qaz';
    nD2:=88;
end;
$$
language plpgsql;

SQL（注意不是psql）对于存储过程out/inout参数返回值的处理，与一般的select查询返回值处理一样，使用PQnfields、PQntuples、PQgetvalue等函数对结果PGresult结构体进行处理。

输出如下：

szd nd2

qaz 88      --名字为啥是qaz？

其它

　　注：postgresql不支持oracle的select bulk collect和fetch bulk collect，但是如果返回多行，没有指定strict的情况下，只返回第1行，但是不会报错。

通过数组也可以支持该特性。