I am wondering if there are open source applications that make extensive uses of the special functions (the ST_* stored procedures) provided by PostGIS? I found many apps that support storing map data in PostGIS, but they seem to just fetch and store data from PostGIS and then operate on them in application code. I would like to learn about how to use those special functions as part of the queries by looking at sample applications. Thanks!
What you are talking about is not stored procedures but spatial inbuilt functions.
Those functions are very powerful and you can do most of the things that you do in a desktop gis directly in the database. It is often faster and more effective, especially when handling huge data sets.
From the same authors a great book including almost everything you need to know about PostGIS: PostGIS in Action
PostGISonline.org is a site I wrote a few years ago to make it easy to discover the beauty of PostGIS online without installing anything. But the site is in quite bad shape and needs a complete makeover. It is mostly working, but have had some downtime lately. Hopefully a new version will be released some day.
QuantumGIS is one candidate. Check out for example the explicit uses in the sqlanywhere plugin and provider, eg.
So, if your application won't be in either language (GEOS has no other bindings), try taking a look at some scripting language GIS projects. Shapely has no PostGIS support, and like OGR, it also uses GEOS directly; other projects don't come to mind. Geoserver's GSS module is using PostGIS, but I haven't found how (do check).