Above and beyond SQL-92/SQL-99, PostgreSQL does a good job of implementing the non-optional parts of SQL:2003 and SQL:2008 as well, and in that regard are competitive with or better than the commercial alternatives.
PL/SQL is probably unlikely to ever be available in the Open Source PostgreSQL product, but it is a feature of EnterpriseDB, which is a PostgreSQL superset.
INSERT..ON DUPLICATE KEY UPDATE is IIRC similar to the new SQL:2003 MERGE statement, which is on the TODO list for PostgreSQL.
CLUSTER is a subset of Oracle's index-organized tables / SQL Server's clustered index features.
One of the truly innovative features that is arriving is exclusion constraints. If you've ever had to implement a scheduling system that deals with concurrent updates, you'll recognize that PostgreSQL has an absolutely killer feature that makes it trivial to solve the concurrent range-excluded searched update problem without messy application code. This feature is pure gold.
Designing a performance-intensive application that is portable across multiple databases is a frustrating, difficult task. Starting with ANSI/ISO syntax is indeed a great way to base your design, but the devil is truly in the details. ORMs can hurt as much as they help.