splitting characters using function in PostgreSQL-CodePudding

I tried to write function like this in PostgreSQL but I'm getting error like

ERROR: syntax error at or near "elems"
LINE 22: RETURN elems;

I want get output like

input: we@@@ty;rer@@2hjjj output:

we@@@ty 
rer@@2hjjj

please help me to solve this error

CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS text[]
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
   delimeter text;  
    elems text[];
    var text;
arr_len int;
  BEGIN
SELECT unnest(string_to_array(inputstr,delimeter))
INTO elems

   RETURN elems;
  END
$BODY$;

CodePudding user response：

Your function is defined to return an array, however unnest would turn the result of creating the array into rows of strings. There is also no need to duplicate the parameter definition as local variables in a DECLARE block. And as you don't seem to want to manipulate the created array somehow, there is no need to store it in a local variable.

It seems you just want:

CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS text[]
    LANGUAGE plpgsql
    immutable
AS $BODY$
BEGIN
   return string_to_array(inputstr,delimeter);
END
$BODY$;

Or simpler as a SQL function:

CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS text[]
    LANGUAGE sql
    immutable
AS 
$BODY$
 select string_to_array(inputstr,delimeter);
$BODY$;

Note that the language name is an identifier and should not be enclosed in single quotes. This syntax is deprecated and support for it will be removed in a future Postgres version.

Edit:

It seems you don't actually want an array, but one row per element after splitting the input value. In that case the function should be declared as returns table() not returns text[]

CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS table(element text)
    LANGUAGE sql
    immutable
AS 
$BODY$
 select unnest(string_to_array(inputstr,delimeter));
$BODY$;

Then use it like this:

select *
from fn_split('we@@@ty;rer@@2hjjj', ';');

Since Postgres 14

 select unnest(string_to_array(inputstr,delimeter));

can be simplified to

select string_to_table(inputstr,delimeter);

CodePudding user response：

CREATE OR REPLACE FUNCTION public.fn_split(
    inputstr text,
    delimeter text)
    RETURNS text[]
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE SECURITY DEFINER 

AS $BODY$
DECLARE 
 elems text[];
BEGIN 
 SELECT string_to_array(inputstr,delimeter) INTO elems;
 RETURN elems;
END; 
$BODY$;

Now call this function like this

SELECT UNNEST(fn_split('1,2,3',',')) as retval