-- Step 1: Create library \set libfile '\'/home/bryan/udx/pyPartitions.py\'' CREATE LIBRARY PyPartitionFunctions AS :libfile LANGUAGE 'Python'; -- Step 2: Create functions CREATE TRANSFORM FUNCTION pyPartitions AS NAME 'pyPartitionsFactory' LIBRARY PyPartitionFunctions; -- Step 3: Use Functions CREATE TABLE txns (year INTEGER, region VARCHAR, txn INTEGER, amount FLOAT); INSERT INTO txns VALUES (2010,'NY',1,12.34); INSERT INTO txns VALUES (2010,'NY',2,23.45); INSERT INTO txns VALUES (2010,'NY',3,34.56); INSERT INTO txns VALUES (2011,'NY',1,12.34); INSERT INTO txns VALUES (2011,'NY',2,23.45); INSERT INTO txns VALUES (2011,'NY',3,34.56); INSERT INTO txns VALUES (2012,'NY',1,45.67); INSERT INTO txns VALUES (2012,'NY',2,23.45); INSERT INTO txns VALUES (2012,'NY',3,34.56); INSERT INTO txns VALUES (2010,'CT',1,12.34); INSERT INTO txns VALUES (2010,'CT',2,23.45); INSERT INTO txns VALUES (2010,'CT',3,34.56); INSERT INTO txns VALUES (2011,'CT',1,12.34); INSERT INTO txns VALUES (2011,'CT',2,23.45); INSERT INTO txns VALUES (2011,'CT',3,34.56); INSERT INTO txns VALUES (2012,'CT',1,45.67); INSERT INTO txns VALUES (2012,'CT',2,23.45); INSERT INTO txns VALUES (2012,'CT',3,34.56); INSERT INTO txns VALUES (2010,'NJ',1,12.34); INSERT INTO txns VALUES (2010,'NJ',2,23.45); INSERT INTO txns VALUES (2010,'NJ',3,34.56); INSERT INTO txns VALUES (2011,'NJ',1,12.34); INSERT INTO txns VALUES (2011,'NJ',2,23.45); INSERT INTO txns VALUES (2011,'NJ',3,34.56); INSERT INTO txns VALUES (2012,'NJ',1,45.67); INSERT INTO txns VALUES (2012,'NJ',2,23.45); INSERT INTO txns VALUES (2012,'NJ',3,34.56); -- without partition SELECT pyPartitions(txn, amount) OVER() FROM txns; -- with partition SELECT year, pyPartitions(txn, amount) OVER(PARTITION BY year) FROM txns; SELECT year, region, pyPartitions(txn, amount) OVER(PARTITION BY year, region) FROM txns; -- Step 4: clean up DROP TABLE txns; DROP LIBRARY PyPartitionFunctions CASCADE;