code_junkie

How can I pass an “array” of values to my stored procedure?

Nov 13th, 2011
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.87 KB | None | 0 0
  1. CREATE TYPE test as (
  2. n int4,
  3. m int4
  4. );
  5.  
  6. select ARRAY[
  7. row(1,2)::test,
  8. row(3,4)::test,
  9. row(5,6)::test
  10. ];
  11.  
  12. CREATE OR REPLACE FUNCTION test_test(IN work_array test[]) RETURNS INT4 as $$
  13. DECLARE
  14. i INT4;
  15. result INT4 := 0;
  16. BEGIN
  17. FOR i IN SELECT generate_subscripts( work_array, 1 ) LOOP
  18. result := result + work_array[i].n * work_array[i].m;
  19. END LOOP;
  20. RETURN result;
  21. END;
  22. $$ language plpgsql;
  23.  
  24. # SELECT test_test(
  25. ARRAY[
  26. row(1, 2)::test,
  27. row(3,4)::test,
  28. row(5,6)::test
  29. ]
  30. );
  31. test_test
  32. -----------
  33. 44
  34. (1 row)
  35.  
  36. DECLARE @i INT
  37. DECLARE @Item NVARCHAR(4000)
  38.  
  39. SET @tempItemList = REPLACE (@tempItemList, ' ', '')
  40. SET @i = CHARINDEX(@delimiter, @tempItemList)
  41.  
  42. WHILE (LEN(@tempItemList) > 0)
  43. BEGIN
  44. IF @i = 0
  45. SET @Item = @tempItemList
  46. ELSE
  47. SET @Item = LEFT(@tempItemList, @i - 1)
  48. INSERT INTO @IDTable(Item) VALUES(@Item)
  49. IF @i = 0
  50. SET @tempItemList = ''
  51. ELSE
  52. SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
  53. SET @i = CHARINDEX(@delimiter, @tempItemList)
  54. END
  55. RETURN
  56.  
  57. CREATE OR REPLACE FUNCTION fnExplode(in_array anyarray) RETURNS SETOF ANYELEMENT AS
  58. $$
  59. SELECT ($1)[s] FROM generate_series(1,array_upper($1, 1)) AS s;
  60. $$
  61. LANGUAGE SQL IMMUTABLE;
  62.  
  63. SET v_remainder = v_id_arr;
  64. SET v_cur_position = 1;
  65.  
  66. WHILE CHAR_LENGTH(v_remainder) > 0 AND v_cur_position > 0 DO
  67. SET v_cur_position = INSTR(v_remainder, '|');
  68. IF v_cur_position = 0 THEN
  69. SET v_cur_string = v_remainder;
  70. ELSE
  71. SET v_cur_string = LEFT(v_remainder, v_cur_position - 1);
  72. END IF;
  73.  
  74. IF TRIM(v_cur_string) != '' THEN
  75. INSERT INTO tmp_test
  76. (id)
  77. VALUES
  78. (v_cur_string);
  79. END IF;
  80.  
  81. SET v_remainder = SUBSTRING(v_remainder, v_cur_position + 1);
  82. END WHILE;
  83.  
  84. SELECT
  85. id
  86. FROM
  87. tmp_test;
  88.  
  89. DROP TEMPORARY TABLE tmp_test;
Add Comment
Please, Sign In to add comment