本文介绍pipelined的使用场景。

背景说明

Oracle中使用pipelined实现流式返回多条记录,在实际应用中经常使用。在PolarDB O引擎中不支持pipelined语法,但可以使用setof实现同样功能。

解决方案

对于Oracle中Pipelined语法,在PolsrDB-O创建函数时,使用Setof语法替换:
CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

rettype
The return data type (optionally schema-qualified). The return type can be a base, composite, or domain type, or can reference the type of a table column. Depending on the implementation language it might also be allowed to specify “pseudo-types” such as cstring. If the function is not supposed to return a value, specify void as the return type.

When there are OUT or INOUT parameters, the RETURNS clause can be omitted. If present, it must agree with the result type implied by the output parameters: RECORD if there are multiple output parameters, or the same type as the single output parameter.

The SETOF modifier indicates that the function will return a set of items, rather than a single item.

The type of a column is referenced by writing table_name.column_name%TYPE.

详情请参见https://www.postgresql.org/docs/11/sql-alteraggregate.html

示例

  • 在Oracle中:
    create or replace function split   
    (   
      p_list varchar2,   
      p_del varchar2 := ','   
    ) return split_tbl pipelined   
    is   
      l_idx pls_integer;   
      l_list varchar2(32767) := p_list;   
      l_value varchar2(32767);   
    begin   
      loop   
        l_idx := instr(l_list,p_del);   
        if l_idx > 0 then   
          pipe row(trim(substr(l_list,1,l_idx-1)));   
          l_list := substr(l_list,l_idx+length(p_del));   
        else   
          pipe row(trim(l_list));   
          exit;   
        end if;   
      end loop;   
      return;   
    end split;  
  • 在PolarDB O引擎中:
    create or replace function rsf1(id int) returns setof int as $$  
    declare  
    begin  
      for i in 0..abs(id) loop  
        return next i;  
      end loop;  
    end;  
    $$ language plpgsql strict;