postgres PL/pgSQL vs oracle PL/SQL

[redss]

Does anybody have experience writing stored procedures in both oracle's PL/SQL and Postgres PL/pgSQL? I have heard that postgres is modeled after oracle's. What are the fundamental differences?

Also I wonder why postgres isn't as widespread as MYSQL... since their both open source but postgres is so much more advanced, how come most ISP's I find offer MYSQL instead?

 

[Leland123]

Hi,

I think most ISP offered MySQL because it was a simpler tool. Its easy to administer. The ISP could assign a single database to the user, and that all the user would see. Most users that pay an ISP something like 19.95 per month for 50 megs of disk space are not doing anything complicated with their backend database. Most web site of this type might have nothing more that a registration form which a visitor could complete and submit the info to the database. Site of this type would not usually have any heavy traffic, probably not more that two or three concurrent user at a time, who would mostly perform light weight selects from the backend on a read only basis. MySQL is perfect for this scenaio.

However, The user who need a dedicated service and has concurrent user in the hundreds performing both reads and writes in a transactions intensive enterprice class application would benefit greatly by using postgres rather that MySQL.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[rycamor]

Comparing PostgreSQL to MySQL is like comparing a McDonalds to a good steak house. They each offer beef, but they each apply to different scenarios ;-).

In answer to your question about the difference between PostgreSQL's procedural language and Oracle's:

Yes, the general approach to PL/pgSQL is very much like PL/SQL, but it is apparently not as mature a language. In the latest version (7.3), they have fixed many things, but still, there are a few things missing (quoted from a knowledgeable Oracle/PostgreSQL user I was chatting with):

1. The number of parameters is limited to 32 (formerly 16)

2. It also does not allow for exception handling and recovering from simple problems like constraint violations.

3. PostgreSQL also does not support named parameters, passing by name, autonomous transactions, paramaterized cursors, output parms, etc...

However, PostgreSQL does have a couple things that Oracle does not have:

1. Several procedural languages: SQL, PL/pgSQL, Perl, Python, TCL, (and Java, coming soon, which will allow for exception handling, I imagine). Personally, I think it is great to have Perl as a PL. It gives you an amazing amount of flexibility for handling complex logic and text manipulation. And, if you install the Perl language handler in "untrusted" mode, you have total freedom for your stored procedure to interact with the Unix environment, such as sending you an email from a trigger, system administration, etc...

2. Query rewrite rules. This can be thought of as a more elegant way to handle many things that would normally be done with triggers. It is a way to "redirect" a query, so that what is done on the front end translates to a different query (or queries) on the back end. Great for updating views, logging, adding to query logic, etc... -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Suspicion is Trust"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)