Using a collection
Hello
Isn't it possible to use a collection as a variable for an SPL-Function
and return these collection?
CREATE FUNCTION neighbors( object INT, distance FLOAT )
RETURNING neighbors_coll <=========================SPECIFIC neighbors2;
DEFINE neighbors_coll SET( INTEGER NOT NULL );
DELETE FROM candidates;
DELETE FROM TABLE( neighbors_coll );
INSERT INTO candidates( oid_1, oid_2, dist )
SELECT graph.oid_1, graph.oid_2, graph.dist
FROM entfernung50 graph
WHERE object = graph.oid_1;
INSERT INTO TABLE( neighbors_coll )
SELECT candidates.oid_1
FROM canddidates
WHERE candidates.dist <= distance;
RETURN neighbors_coll;
END FUNCTION
Thanks!
Peter Hamm
Peter Hamm wrote:
| Isn't it possible to use a collection as a variable for an SPL-Function
and return these collection?
-- About:
--
-- This file contains a script demonstrating how to create and use the
-- COLLECTIONs data types in IDS/UD.
--
--------------------------- Example Schema
--------------------------------
--
CREATE ROW TYPE Name_Value_Pair (
Name VARCHAR(12) NOT NULL,
Value INTEGER NOT NULL
);
GRANT USAGE ON TYPE Name_Value_Pair TO PUBLIC;--
CREATE TABLE Collection_Examples (
Id INTEGER NOT NULL PRIMARY KEY,
List_Example LIST( VARCHAR(16) NOT NULL ),
Set_Example SET( INTEGER NOT NULL ),
MSet_Example MULTISET( Name_Value_Pair NOT NULL )
);
GRANT ALL ON Collection_Examples TO PUBLIC;--
--------------------------- Example Data ---------------------------------
--
-- This query illustrates several literals.
--
INSERT INTO Collection_Examples
VALUES
( 1,
LIST{'Fred','Barney','Wilma','Betty'},
SET{1,2,3,4,5,6,7,8,9,10},
MULTISET{ ROW('Stars',3)::Name_Value_Pair,
ROW('Forks',4)::Name_Value_Pair,
ROW('Stars',3)::Name_Value_Pair}
);--
INSERT INTO Collection_Examples
VALUES
( 2,
LIST{'Willie','Road'},
SET{1,2,3,4,5,6},
MULTISET{ ROW('Stars',3)::Name_Value_Pair,
ROW('Forks',1)::Name_Value_Pair}
);--
INSERT INTO Collection_Examples
VALUES
( 3,
LIST{'Sylvester','Tweetie'},
SET{1,2,3,4,5,6},
MULTISET{ ROW('Stars',2)::Name_Value_Pair,
ROW('Forks',2)::Name_Value_Pair,
ROW('Birdcage',7)::Name_Value_Pair}
);--
-------------------------------------------------------------------------------
--
-- SPL example:
--
CREATE FUNCTION Collection_Construct ( Arg1 INTEGER )
RETURNING SET(INTEGER NOT NULL )
DEFINE stRetVal SET(INTEGER NOT NULL );
LET stRetVal = SET{1,2,3,4,5};
RETURN stRetVal;
END FUNCTION;
GRANT EXECUTE ON FUNCTION Collection_Construct ( INTEGER ) TO PUBLIC;--
-------------------------- SQL Example for CARDINALITY
---------------------
--
SELECT C.Id,
CARDINALITY ( C.List_Example ) AS Num_in_List,
CARDINALITY ( C.Set_Example ) AS Num_in_Set,
CARDINALITY ( C.MSet_Example ) AS Num_in_MSet
FROM Collection_Examples C;--
------------------------- SQL Example for Accessing Innards
------------------
--
SELECT Id
FROM Collection_Examples
WHERE 'Betty' IN List_Example;--
-------------------------- LIST Examples ---------------------------------
--
CREATE TABLE Departments (
Name VARCHAR(24) NOT NULL,
Sales LIST( INTEGER NOT NULL )
);
GRANT ALL ON Departments TO PUBLIC;--
INSERT INTO Departments
VALUES
( 'Shoe',LIST{12000,13000,12500,13500,14000,14500,14750,15000,14500,14500,13000,12250}
);
INSERT INTO Departments
VALUES
( 'Suits',LIST{22000,23000,22500,23500,24000,24500,24750,25000,24500,24500,23000,22250}
);
--
-------------------------------- Using COLLECTIONS
------------------------
--
DROP FUNCTION Intersects ( SET(lvarchar NOT NULL),
SET(lvarchar NOT NULL));
CREATE FUNCTION Intersects ( Arg1 SET(lvarchar NOT NULL),
Arg2 SET(lvarchar NOT NULL))
RETURNING boolean
DEFINE nQuery INTEGER;
LET nQuery = ( SELECT COUNT(*)
FROM TABLE(Arg1) F,
TABLE(Arg2) S
WHERE F = S
);
IF (nQuery > 0) THEN
RETURN 't'::boolean;
END IF;
RETURN 'f'::boolean;
END FUNCTION;
--
-- An example
--
EXECUTE FUNCTION Intersect (
SET{'seventh','eigth','ninth'}::SET(lvarchar NOT NULL),
); SET{'first','fifth','seventh'}::SET(lvarchar NOT NULL)
--
--
CREATE FUNCTION RemoveElement ( Arg1 SET(lvarchar NOT NULL),
Arg2 lvarchar )
RETURNING SET(lvarchar NOT NULL)
DEFINE setRetVal SET(lvarchar NOT NULL);
DEFINE lvCurrent LVARCHAR;
LET setRetVal = Arg1;
FOREACH Cursor FOR ( SELECT S
INTO lvCurrent
FROM TABLE(setRetVal) S
WHERE S = Arg2 )
DELETE FROM Table(setRetVal) WHERE CURRENT OF Cursor; END FOREACH;
RETURN setRetVal;
END FUNCTION;
--
DROP FUNCTION RemoveElement ( SET(LVARCHAR NOT NULL), LVARCHAR );--
CREATE FUNCTION RemoveElement ( Arg1 SET(LVARCHAR NOT NULL),
Arg2 LVARCHAR )
RETURNS SET(LVARCHAR NOT NULL)
DEFINE setRetVal SET(LVARCHAR NOT NULL);
DEFINE lvCurrent LVARCHAR;
FOREACH Cursor FOR SELECT * INTO lvCurrent FROM TABLE( Arg1 )
IF ( lvCurrent != Arg2 ) THEN
INSERT INTO TABLE(setRetVal) VALUES ( lvCurrent); END IF;
END FOREACH;
RETURN setRetVal;
END FUNCTION;
--
EXECUTE FUNCTION RemoveElement ( SET{'Hello', 'Good-Bye', 'So Long' },
'Hello');--
-- CommonElemCount ()
--
-- This user-defined function takes two arguments which are SETS of
-- INTEGERS, and a third argument which is another INTEGER. The function
counts
-- the number of elements that are common to both. If this count exceeds
the
-- third argument, the function returns true. Otherwise, it returns
false.
--
CREATE FUNCTION CommonElemCount ( First_Set SET(INTEGER NOT NULL),
Second_Set SET(INTEGER NOT NULL),
Common_Num INTEGER )RETURNS boolean
DEFINE i INTEGER;
DEFINE j INTEGER;
DEFINE nCnt INTEGER;
LET nCnt = 0;
FOREACH SELECT F1.Num INTO i
FROM TABLE( First_Set ) F1 ( Num )
FOREACH SELECT F2.Num INTO j
FROM TABLE( Second_Set ) F2 ( Num )
IF ( i = j ) THEN
LET nCnt = nCnt + 1;
EXIT FOREACH;
END IF;
END FOREACH;
IF ( nCnt = Common_Num ) THEN
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH;
IF ( nCnt = Common_Num) THEN
RETURN 't'::boolean;
END IF;
RETURN 'f'::boolean;
END FUNCTION;
--
EXECUTE FUNCTION CommonElemCount ( SET{1,2,3,4,5}, SET{3,4,5,6}, 2);
EXECUTE FUNCTION CommonElemCount ( SET{1,2,3,4,5}, SET{3,4,5,6}, 4);--
DROP FUNCTION CommonElemCount ( SET(INTEGER NOT NULL),
SET(INTEGER NOT NULL),
INTEGER );
General Notes:
1. COLLECTIONS can also be handled in 'C' UDRs, and in general the
performance of 'C' for this
task is pretty good. Of course, 'C' is much harder to use than SPL.
2. COLLECTIONS can be hacked about using SQL, too. In 9.14, you had to
use SPL a lot to
work with them. This isn't the case in IDS.2000.
Hope this helps!
KR
Pb
Peter Hamm wrote:
| Isn't it possible to use a collection as a variable for an SPL-Function
and return these collection?
-- About:
--
-- This file contains a script demonstrating how to create and use the
-- COLLECTIONs data types in IDS/UD.
--
--------------------------- Example Schema
--------------------------------
--
CREATE ROW TYPE Name_Value_Pair (
Name VARCHAR(12) NOT NULL,
Value INTEGER NOT NULL
);
GRANT USAGE ON TYPE Name_Value_Pair TO PUBLIC;--
CREATE TABLE Collection_Examples (
Id INTEGER NOT NULL PRIMARY KEY,
List_Example LIST( VARCHAR(16) NOT NULL ),
Set_Example SET( INTEGER NOT NULL ),
MSet_Example MULTISET( Name_Value_Pair NOT NULL )
);
GRANT ALL ON Collection_Examples TO PUBLIC;--
--------------------------- Example Data ---------------------------------
--
-- This query illustrates several literals.
--
INSERT INTO Collection_Examples
VALUES
( 1,
LIST{'Fred','Barney','Wilma','Betty'},
SET{1,2,3,4,5,6,7,8,9,10},
MULTISET{ ROW('Stars',3)::Name_Value_Pair,
ROW('Forks',4)::Name_Value_Pair,
ROW('Stars',3)::Name_Value_Pair}
);--
INSERT INTO Collection_Examples
VALUES
( 2,
LIST{'Willie','Road'},
SET{1,2,3,4,5,6},
MULTISET{ ROW('Stars',3)::Name_Value_Pair,
ROW('Forks',1)::Name_Value_Pair}
);--
INSERT INTO Collection_Examples
VALUES
( 3,
LIST{'Sylvester','Tweetie'},
SET{1,2,3,4,5,6},
MULTISET{ ROW('Stars',2)::Name_Value_Pair,
ROW('Forks',2)::Name_Value_Pair,
ROW('Birdcage',7)::Name_Value_Pair}
);--
-------------------------------------------------------------------------------
--
-- SPL example:
--
CREATE FUNCTION Collection_Construct ( Arg1 INTEGER )
RETURNING SET(INTEGER NOT NULL )
DEFINE stRetVal SET(INTEGER NOT NULL );
LET stRetVal = SET{1,2,3,4,5};
RETURN stRetVal;
END FUNCTION;
GRANT EXECUTE ON FUNCTION Collection_Construct ( INTEGER ) TO PUBLIC;--
-------------------------- SQL Example for CARDINALITY
---------------------
--
SELECT C.Id,
CARDINALITY ( C.List_Example ) AS Num_in_List,
CARDINALITY ( C.Set_Example ) AS Num_in_Set,
CARDINALITY ( C.MSet_Example ) AS Num_in_MSet
FROM Collection_Examples C;--
------------------------- SQL Example for Accessing Innards
------------------
--
SELECT Id
FROM Collection_Examples
WHERE 'Betty' IN List_Example;--
-------------------------- LIST Examples ---------------------------------
--
CREATE TABLE Departments (
Name VARCHAR(24) NOT NULL,
Sales LIST( INTEGER NOT NULL )
);
GRANT ALL ON Departments TO PUBLIC;--
INSERT INTO Departments
VALUES
( 'Shoe',LIST{12000,13000,12500,13500,14000,14500,14750,15000,14500,14500,13000,12250}
);
INSERT INTO Departments
VALUES
( 'Suits',LIST{22000,23000,22500,23500,24000,24500,24750,25000,24500,24500,23000,22250}
);
--
-------------------------------- Using COLLECTIONS
------------------------
--
DROP FUNCTION Intersects ( SET(lvarchar NOT NULL),
SET(lvarchar NOT NULL));
CREATE FUNCTION Intersects ( Arg1 SET(lvarchar NOT NULL),
Arg2 SET(lvarchar NOT NULL))
RETURNING boolean
DEFINE nQuery INTEGER;
LET nQuery = ( SELECT COUNT(*)
FROM TABLE(Arg1) F,
TABLE(Arg2) S
WHERE F = S
);
IF (nQuery > 0) THEN
RETURN 't'::boolean;
END IF;
RETURN 'f'::boolean;
END FUNCTION;
--
-- An example
--
EXECUTE FUNCTION Intersect (
SET{'seventh','eigth','ninth'}::SET(lvarchar NOT NULL),
); SET{'first','fifth','seventh'}::SET(lvarchar NOT NULL)
--
--
CREATE FUNCTION RemoveElement ( Arg1 SET(lvarchar NOT NULL),
Arg2 lvarchar )
RETURNING SET(lvarchar NOT NULL)
DEFINE setRetVal SET(lvarchar NOT NULL);
DEFINE lvCurrent LVARCHAR;
LET setRetVal = Arg1;
FOREACH Cursor FOR ( SELECT S
INTO lvCurrent
FROM TABLE(setRetVal) S
WHERE S = Arg2 )
DELETE FROM Table(setRetVal) WHERE CURRENT OF Cursor; END FOREACH;
RETURN setRetVal;
END FUNCTION;
--
DROP FUNCTION RemoveElement ( SET(LVARCHAR NOT NULL), LVARCHAR );--
CREATE FUNCTION RemoveElement ( Arg1 SET(LVARCHAR NOT NULL),
Arg2 LVARCHAR )
RETURNS SET(LVARCHAR NOT NULL)
DEFINE setRetVal SET(LVARCHAR NOT NULL);
DEFINE lvCurrent LVARCHAR;
FOREACH Cursor FOR SELECT * INTO lvCurrent FROM TABLE( Arg1 )
IF ( lvCurrent != Arg2 ) THEN
INSERT INTO TABLE(setRetVal) VALUES ( lvCurrent); END IF;
END FOREACH;
RETURN setRetVal;
END FUNCTION;
--
EXECUTE FUNCTION RemoveElement ( SET{'Hello', 'Good-Bye', 'So Long' },
'Hello');--
-- CommonElemCount ()
--
-- This user-defined function takes two arguments which are SETS of
-- INTEGERS, and a third argument which is another INTEGER. The function
counts
-- the number of elements that are common to both. If this count exceeds
the
-- third argument, the function returns true. Otherwise, it returns
false.
--
CREATE FUNCTION CommonElemCount ( First_Set SET(INTEGER NOT NULL),
Second_Set SET(INTEGER NOT NULL),
Common_Num INTEGER )RETURNS boolean
DEFINE i INTEGER;
DEFINE j INTEGER;
DEFINE nCnt INTEGER;
LET nCnt = 0;
FOREACH SELECT F1.Num INTO i
FROM TABLE( First_Set ) F1 ( Num )
FOREACH SELECT F2.Num INTO j
FROM TABLE( Second_Set ) F2 ( Num )
IF ( i = j ) THEN
LET nCnt = nCnt + 1;
EXIT FOREACH;
END IF;
END FOREACH;
IF ( nCnt = Common_Num ) THEN
EXIT FOREACH;
ELSE
CONTINUE FOREACH;
END IF;
END FOREACH;
IF ( nCnt = Common_Num) THEN
RETURN 't'::boolean;
END IF;
RETURN 'f'::boolean;
END FUNCTION;
--
EXECUTE FUNCTION CommonElemCount ( SET{1,2,3,4,5}, SET{3,4,5,6}, 2);
EXECUTE FUNCTION CommonElemCount ( SET{1,2,3,4,5}, SET{3,4,5,6}, 4);--
DROP FUNCTION CommonElemCount ( SET(INTEGER NOT NULL),
SET(INTEGER NOT NULL),
INTEGER );
General Notes:
1. COLLECTIONS can also be handled in 'C' UDRs, and in general the
performance of 'C' for this
task is pretty good. Of course, 'C' is much harder to use than SPL.
2. COLLECTIONS can be hacked about using SQL, too. In 9.14, you had to
use SPL a lot to
work with them. This isn't the case in IDS.2000.
Hope this helps!
KR
Pb