Question 115067 in PostgreSQL Question posted in
The official documentation can be found here.

splitting characters using function in PostgreSQL

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$;
Tags:

2

Answers


  1. 0 
    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

    enter image description here

    Above is the screenshot which includes function definition and in the first list the command to call this function

    Here is the command that you need to execute in order to call this function in the PostgreSQL query window after the creation of the function.

    enter image description here

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search