Not sure this is standard SQL, but something I wanted to do is be able to cast new columns as a defined type while creating them on the fly with a SELECT statement.  In this way, I might, for example, UNION or UNION ALL two dissimilar tables on the fly with a VIEW.  In PostgreSQL this can be done easily, e.g.:

SELECT 1::smallint as gid, 'aaaah!'::text as mytextfield, a.gid as gid1, a.the_geom
	FROM base.wetlands_06 AS a;

The “::” serves as the cast.  I’ll have an upcoming post restructuring our data for serving in GeoServer as a VIEW to maximize legend support for GeoExt.  John, one of my interns, will be the guest blogger.