Background information

pipelined is commonly used in Oracle to return multiple records in the stream. The pipelined syntax is not supported in PolarDB O Edition. 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 Edition
    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;