Background information

pipelined is commonly used in Oracle to return multiple records in the stream. The pipelined syntax is not supported in PolarDB-O. However, you can use setof to implement the same function.

Solutions

For the Pipelined syntax in Oracle, when creating a function in PolsrDB-O, the Setof syntax is used to replace:
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.

For more information, see https://www.postgresql.org/docs/11/sql-alteraggregate.html.

Examples

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