Here is some migration information that I used a few years ago when I did some migration work. I don't remember the details but here are a few recent links to such information:
From Postgres own site: http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html
There are several more entries on that topic. Just search for it on the PostgreSQL site.
A quick summary of common conversion issues:
http://www.cs.cmu.edu/~pmerson/docs/OracleToPostgres.pdf
An old document from this site:
https://openacs.org/doc/openacs-3/html/oracle-to-pg-porting.html
Regarding converting "connect by" from Oracle, here is an example that I believe is accurate. You can use the implementation tree_sortkey that is used in several packages in OACS to create a hierarchy similar to what you can achieve with "start with...connect by" in Oracle.
<fullquery name="package_recreate_hierarchy.select_object_types">
<querytext>
select t.object_type
from acs_object_types t
where t.dynamic_p = 't'
start with t.object_type = :object_type
connect by prior t.object_type = t.supertype
</querytext>
</fullquery><fullquery name="package_recreate_hierarchy.select_object_types">
<querytext>
select t2.object_type
from acs_object_types t1, acs_object_types t2
where t2.dynamic_p = 't'
and t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)
and t1.object_type = :object_type
</querytext>
</fullquery>
I hope this information helps. It will take time to convert complex queries, otherwise it's rather straightforward.
But then again, you might be looking at a dump and load or similar and I have no personal experience with moving a whole installation of OACS between databases. I am guessing that you need to create your own export and import scripts if that's the case.
