Click here to Skip to main content
15,887,175 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Below is the function return in SQL

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[Lk_Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX), rn int)       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)   
    declare @rn int = 1 -- row number that increments with each value in the delimited string

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)   
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items, rn) values(@slice, @rn)       

        set @String = right(@String,len(@String) - @idx)       
        set @rn = @rn +1

        if len(@String) = 0 break       
    end   
return 
end
GO


I need to convert this function to Postgress database to work it in that DB.

What I have tried:

I have tried the below

CREATE FUNCTION LK_Split(String varchar(5000), Delimiter char(1))       
returns table       
language plpgsql
as
$$    
declare
idx integer;      
slice varchar(8000); 
table1 table; 

select idx = 1       
if length(String)<1 or String is null  return       

while idx!= 0       
begin       
set idx = charindex(Delimiter,String);      
if idx!=0       
set slice = left(String,idx - 1;   
else       
set slice = String  ;     

if(length(slice)>0)  
select values(slice) into temptable(Items) from table1
return table1;

set String = right(String,length(String) - idx)       
if length(String) = 0 break ;      
end;
end;
$$;


But it is not working due to return of table value.
Posted
Updated 7-Aug-20 4:03am
Comments
CHill60 7-Aug-20 5:30am    
Please clarify what "not working due to return of table value" means
Member 11776570 7-Aug-20 5:36am    
Just i need to convert that SQL function to Postgres, so can't able to convert it. Please if you can convert it or make same function that works in postgres database.
CHill60 7-Aug-20 5:42am    
What is wrong with the code you have already converted?
We do this in our spare time and are more than happy to help but we don't do your work for you. It's also very difficult to rewrite sql without table schemas, sample data or expected results.
You have also done some work but claim it doesn't work, but I don't understand the sentence "But it is not working due to return of table value." So just clarify what that means.
By the way - if you use the reply button to a post then the member is notified of your reply
Member 11776570 7-Aug-20 5:50am    
This sentence "But it is not working due to return of table value." means the return table that i used it is not working in Postgres. I want that same function in Postgres nothiugn else.
Below is the sample data.
(A,A,A,C,C,C,C,C,A,A,"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","")

1 solution

Your Create Function code is incorrect - see PostgreSQL: Documentation: 9.4: CREATE FUNCTION[^].
You have not defined the column list.
For an example see PL/pgSQL Function Returns A Table[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900