Porting Postgres favorites to Oracle

30 07 2009

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.

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
FROM test;
-- 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:

  p_start         NUMBER,
  p_end           NUMBER
) RETURN number_table PIPELINED AS
  FOR i IN p_start .. p_end LOOP
    PIPE ROW(i);




9 responses

8 08 2009
Gurjeet Singh

In a normal world, I would have expected to see an Oracle functionality being implemented for Postgres (Commercial to open-source port).

Nice post.

19 08 2009

Is there any Postgres 8.3 equivalent to:

SELECT * FROM unnest(num_array);

How can you do this in Postgres 8.3? I’m trying to port some Oracle code to Postgres that uses datatypes of TABLE TYPE OF VARCHAR2(20) and one of the pieces of code uses this type as part of a sub-query like:

select lname from names where lname in (select * from table ( cast (mycustomtype as mytype) ) );

Is there anyway to select from a variable like this in Postgres?

19 08 2009
Scott Bailey
19 08 2009

That doesn’t quite work.


–Show that values are set
select * from unnest(name_table);

ERROR near line 1:
function unnest(udtabletype) does not exist
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
Where: edb-spl function “plspl_anon_func” line 13 at SQL statement


19 08 2009
Scott Bailey

You’re mixing your metaphors so to speak. And it looks like you are using Enterprise DB, so you can pick one or the other. But don’t mix pg arrays with oracle varrays or nested tables.

From your example I’d guess that you are more familiar with Oracle, so do this:

SELECT * from TABLE(name_table);

20 08 2009

I’m using Enterprisedb and am more familiar with Oracle though. Enterprisedb Postgres doesn’t support the TABLE function which I believe you are suggesting using.

In the Enterprisedb docs, a nested table TABLE TYPE OF is just an associative array, but they don’t really behave like arrays. Otherwise I could just do:

select fname from sometable where fname = any(mynestedtable);

In Oracle, the code we are porting passes in an XML doc to a function and parses out values that are stored in a plsql nested table. Then a cursor queries a database table for records that match the values in the nested table. The part that queries the nested table is:

CURSOR mycur
WHERE sometype IN
FROM TABLE ( cast ( vtemptable AS gstringtable) ) );

In my situation, I think I’m better off just parsing the XML values into a regular array and doing:

CURSOR mycur IS select fname from sometable where fname = any(mynestedtable);

The nested table in our Oracle code is just an object type with one column of varchar and a table of those object types. That’s just an text[] array in Postgres which is what I will use.

20 08 2009
Scott Bailey

Ok, I haven’t used EDB enough to give you any advice on it. I’ve done tons of XML parsing in both Oracle and Pg though. I’d suggest ditching the Oracle extensions though and going straight Pg arrays. Postgres’ array handling is much slicker than Oracle’s ever was.

Pg array’s are generally functional enough that you should be able to do everything in straight SQL rather than jumping to cursors and procedural code.

I’ll send you my email and if you need further assistance we can do it offline.

6 05 2013

This is a pretty old post; but I thought I would give it a try. This is really good stuff. But any idea if the performance of COLLECT and array_agg is comparable atleast in terms of response times? For my sample data (millions of records) in both postgres and oracle, I see a steep difference in both. like 41s with array_agg and 400s with oracle COLLECT. So was wondering if there is any other alternative to COLLECT that I could give a try.

6 05 2013
Scott Bailey

You’re stuck with COLLECT in Oracle. The trick is not to aggregate your entire table. Typically you are grouping by something fairly granular, a single employee’s shifts for a pay period, guests checked in to a hotel at a given point in time, etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: