If you're porting Oracle SQL to Postgres SQL for the ACS/pg, you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.
In this document, we're talking about:
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:
set now [database_to_tcl_string $db "select sysdate from dual"]should become
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.
decode(expr, search, expr[, search, expr...] [, default])To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.
Postgres doesn't have the same construct. It can be replicated with:
CASE WHEN expr THEN expr [...] ELSE expr ENDwhich returns the expression corresponding to the first true predicate. For example:
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END
start_date := NVL(hire_date, SYSDATE);The above statement will return SYSDATE if hire_date is null. Postgres has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.
select a.field1, b.field2 from a, b where a.item_id = b.item_id(+)where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.
Depending on the exact operation performed by the outer join, there are different approaches. For outer joins where specific, raw data is extracted from the outer-joined table (e.g. as above), it's best to use a UNION operation as follows:
select a.field1, b.field2 from a, b where a.item_id = b.item_id UNION select a.field1, NULL as field2 from a where 0= (select count(*) from b where b.item_id=a.item_id)
For queries with quadruple outer-joins, the queries can be quite long! They work quite well, though.
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:
select a.field1, sum(b.field2) from a, b where a.item_id = b.item_id (+) group by a.field1then the Postgres query can look like:
select a.field1, b_sum_field2_by_item_id(a.item_id) from awhere you've defined the function:
create function b_sum_field2_by_item_id(integer)
returns integer
as '
DECLARE
v_item_id alias for $1;
BEGIN
return sum(field2) from b where item_id= v_item_id;
END;
' language 'plpgsql';
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:
create table my_table (
my_key integer primary key,
lob integer references lobs,
my_other_data some_type -- etc
);
create trigger my_table_lob_trig before insert or delete or update
on my_table for each row execute procedure on_lob_ref();
To put a binary file into "my_table":
set lob [database_to_tcl_string $db "select empty_lob()"] ns_db dml $db "begin" ns_db dml $db "update my_table set lob = $lob where my_key = $my_key" ns_pg blob_dml_file $db $lob $tmp_filename ns_db dml $db "end"Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.
To return a large object stored in "my_table" to the user:
set lob [database_to_tcl_string $db "select lob from my_table
where my_key = $my_key"]
ns_pg blob_write $db $lob
Note that you don't need to wrap the call to blob_write in a
transaction, as the database isn't being modified.
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.
One other feature I missed in PostgreSQL is the ability to query across multiple databases. As a workaround, I have renamed my tables with the old database name and an underscore (e.g. TRANSACTIONS_SALES) where it was necessary, and placed them all in the same database. It's just one of those things that's nice to know before you start the conversion.
-- Elaine Lindelef, December 13, 2001
I am having trouble finding information about TABLESPACE support in postgresql. My Oracle schema uses them, is there a subsitute in postgresql?
-- Peter Laird, May 11, 2002
This was good. However has anyone done any work on making a ORACLE to PostgreSQL or vice-versa conversion ?
-- Mayuresh Kadu, May 17, 2002
I have made several conversions from Oracle 7 & 8 to PostgreSQL and one from Oracle 8i to PG 7.2I'm writing all those experiences in a white paper to help as many people as possible.
See my home site http://www.argudo.org, there will be a link to this brand new article, next to the one concerning PostgreSQL tuning howto.
Cheers
-- ARGUDO Jean-Paul, May 28, 2002
Answer to 13/12/2001 question:About querying accros databases see dblink : « Functions returning results from a remote database »
You'll find this project in the CONTRIBs in PostgreSQL sources or in a package of your distribution : postgresql-contrib ...
Cheers
-- ARGUDO Jean-Paul, May 28, 2002