Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TYPE test as (
- n int4,
- m int4
- );
- select ARRAY[
- row(1,2)::test,
- row(3,4)::test,
- row(5,6)::test
- ];
- CREATE OR REPLACE FUNCTION test_test(IN work_array test[]) RETURNS INT4 as $$
- DECLARE
- i INT4;
- result INT4 := 0;
- BEGIN
- FOR i IN SELECT generate_subscripts( work_array, 1 ) LOOP
- result := result + work_array[i].n * work_array[i].m;
- END LOOP;
- RETURN result;
- END;
- $$ language plpgsql;
- # SELECT test_test(
- ARRAY[
- row(1, 2)::test,
- row(3,4)::test,
- row(5,6)::test
- ]
- );
- test_test
- -----------
- 44
- (1 row)
- DECLARE @i INT
- DECLARE @Item NVARCHAR(4000)
- SET @tempItemList = REPLACE (@tempItemList, ' ', '')
- SET @i = CHARINDEX(@delimiter, @tempItemList)
- WHILE (LEN(@tempItemList) > 0)
- BEGIN
- IF @i = 0
- SET @Item = @tempItemList
- ELSE
- SET @Item = LEFT(@tempItemList, @i - 1)
- INSERT INTO @IDTable(Item) VALUES(@Item)
- IF @i = 0
- SET @tempItemList = ''
- ELSE
- SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
- SET @i = CHARINDEX(@delimiter, @tempItemList)
- END
- RETURN
- CREATE OR REPLACE FUNCTION fnExplode(in_array anyarray) RETURNS SETOF ANYELEMENT AS
- $$
- SELECT ($1)[s] FROM generate_series(1,array_upper($1, 1)) AS s;
- $$
- LANGUAGE SQL IMMUTABLE;
- SET v_remainder = v_id_arr;
- SET v_cur_position = 1;
- WHILE CHAR_LENGTH(v_remainder) > 0 AND v_cur_position > 0 DO
- SET v_cur_position = INSTR(v_remainder, '|');
- IF v_cur_position = 0 THEN
- SET v_cur_string = v_remainder;
- ELSE
- SET v_cur_string = LEFT(v_remainder, v_cur_position - 1);
- END IF;
- IF TRIM(v_cur_string) != '' THEN
- INSERT INTO tmp_test
- (id)
- VALUES
- (v_cur_string);
- END IF;
- SET v_remainder = SUBSTRING(v_remainder, v_cur_position + 1);
- END WHILE;
- SELECT
- id
- FROM
- tmp_test;
- DROP TEMPORARY TABLE tmp_test;
Add Comment
Please, Sign In to add comment