Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: Create temporary table in function

    Hi
    I am trying to convert a User Defined function from MS SQL server to a Postgresql function. The function takes a parameter of a list of integers and then inserts them in to a temporary table. So far I haven't had much success with trying to do this so I will show you the MS SQL function along with my attempt. If anyone can help me out that would be great.

    Thanks
    Liam

    MS SQL User defined function:
    Code:
    CREATE FUNCTION dbo.fn_ConvertIntergerListoTable 
    (
    	@list ntext
    )
    
    RETURNS @tbl TABLE 
    (
    	number int
    ) 
    
    AS 
    
    BEGIN 
    
    DECLARE 
    	@pos int,
    	@textpos int,
    	@chunklen smallint,
    	@str nvarchar(4000),
    	@tmpstr nvarchar(4000),
    	@leftover nvarchar(4000)
    
    SET @textpos = 1
    SET @leftover = ''
    
    WHILE @textpos <= datalength(@list) / 2
    
       BEGIN
    
    	SET @chunklen = 4000 - datalength(@leftover) / 2
    	SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
    	SET @textpos = @textpos + @chunklen
    
    	SET @pos = charindex(',', @tmpstr)
    
    	WHILE @pos > 0
    
    	   BEGIN
    		SET @str = substring(@tmpstr, 1, @pos - 1)
    		INSERT @tbl (number) VALUES(convert(int, @str))
    		SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
    		SET @pos = charindex(',', @tmpstr)
    	   END
    
             SET @leftover = @tmpstr 
    
       END
    
    IF ltrim(rtrim(@leftover)) <> ''
    	INSERT @tbl (number) VALUES(convert(int, @leftover))
    
    RETURN
    
    END
    Postgresql Function: I am trying to do this by declaring a type of data type table in the declare block, this seems to be whats giving me trouble.
    Code:
    CREATE OR REPLACE FUNCTION fn_ConvertIntergerListoTable 
    (
    	list text
    )
    
    RETURNS table(number int)  
    
    AS 
    $$
    DECLARE 
    	TYPE tbltype IS TABLE OF number%int INDEX BY BINARY INTEGER;
    
    	tbl tbltype;
    	pos int;
    	textpos int;
    	chunklen smallint;
    	str varchar(4000);
    	tmpstr varchar(4000);
    	leftover varchar(4000);
    	
    BEGIN 
    
    textpos = 1;
    leftover = '';
    
    WHILE textpos <= datalength(list) / 2
    
    	chunklen = 4000 - datalength(leftover) / 2;
    	tmpstr = ltrim(leftover || substr(list, textpos, chunklen));
    	textpos = textpos || chunklen;
    
    	pos = charindex(',', tmpstr);
    
    	WHILE pos > 0
    
    	  
    		str = substr(tmpstr, 1, pos - 1);
    		INSERT tbl (number) VALUES str::int;
    		tmpstr = ltrim(substr(tmpstr, pos || 1, length(tmpstr)));
    		pos = charindex(',', tmpstr);
    	   END;
    
             leftover = tmpstr;
    
       END;
    
    IF ltrim(rtrim(leftover)) <> ''
    	INSERT tbl (number) VALUES(leftover::int);
    
    RETURN tbl;
    END IF;
    END;
    $$Language 'plpgsql';

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What do you need the temporary table for?
    It sounds to me as you can get rid of that and simply use generate_series()

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Agree with not needing a temp table. You should almost never need that when working with pg. But it looks like OP is trying to split a comma separated list of ints. You didn't mention your version (below 8.4 won't have unnest). But try this:
    Code:
    SELECT unnest(string_to_array('1,2,3,4,5', ','))::int i;

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Oh, and if you want to continue doing it the hard way, your function def needs to return setof int.

    Code:
    CREATE OR REPLACE FUNCTION i_love_the_ms_way(list text)
    RETURNS SETOF int AS
    ...

  5. #5
    Join Date
    Oct 2009
    Posts
    37
    Ok thanks for this artacus. Yes it is trying to insert a list of ints in to a table. I will give your suggestions a try. Thanks again.

  6. #6
    Join Date
    Jan 2010
    Posts
    4
    Quote Originally Posted by tcliam View Post
    Ok thanks for this artacus. Yes it is trying to insert a list of ints in to a table.
    Maybe you should have asked a different question? Something like...

    "I have a table where I need to insert a list of integers, ranging from x1 to x2, what would be the best approach?"

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I'm guessing tcliam doesn't want a simple range, or else he's REALLY going at it bass ackwards. Its probably a list of ids so '10242,42562,3219,82882' may have been a better example.

  8. #8
    Join Date
    Oct 2009
    Posts
    37
    Code:
    SELECT unnest(string_to_array('1,2,3,4,5', ','))::int i;
    Works great.
    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •