For the last month or so, I have been creating temporal extensions for both Postgres and Oracle. Of the two, the Postgres version was easier to implement because the array functionality in Postgres is the best of any dbms out there. So for other developers who split their time between Postgres and Oracle, here is how to create the equivalent of array_agg(), unnest() and generate_series() in Oracle.
One persistent problem when programming with Oracle is the disjunction between the SQL layer and the PL/SQL layer. For instance, Oracle has boolean and integer data types, but they are only available in PL/SQL. And Oracle’s arrays (varray and nested table) suffer from the same problem. If you want to do much with varrays and nested tables, you generally have to jump out of SQL to PL/SQL.
When you create a data type in Postgres, it automatically creates an array type as well. In Oracle, we’ll have to create it manually.
CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;
Normally when you create a collection (array) in Oracle, you will use BULK COLLECT INTO to store it into a PL/SQL variable. But the point here is that we want to keep the collection in the SQL layer and you do that will the collect function. You might expect your $100K database would be able to figure out what data type it is collecting. Unfortunately, you can’t use collect with out casting the collection.
SELECT CAST(COLLECT(id) AS number_table) num_array
-- Postgres equivalent
SELECT array_agg(id) AS num_array FROM test
So there is half the battle. The other half is exploding an array.
SELECT * FROM TABLE(num_array);
-- Postgres Equivalent
SELECT * FROM unnest(num_array);
Now lets add generate_series() to Oracle. For readers who have never used Postgres, generate_series() is one of the most useful db functions ever. It generates a range of numbers with each number being a row in the result set.
SELECT i, '2009-01-01'::date + i AS dt, chr(64 + i) AS c
FROM generate_series(1,10) i
i dt c
---- --------- ------
1 1/2/2009 A
2 1/3/2009 B
3 1/4/2009 C
4 1/5/2009 D
5 1/6/2009 E
6 1/7/2009 F
7 1/8/2009 G
8 1/9/2009 H
9 1/10/2009 I
10 1/11/2009 J
So here is the function in Oracle:
CREATE OR REPLACE FUNCTION generate_series( p_start NUMBER, p_end NUMBER ) RETURN number_table PIPELINED AS BEGIN FOR i IN p_start .. p_end LOOP PIPE ROW(i); END LOOP; RETURN; END;