It looks like PostgreSQL 9.2 will get a JSON core type. Additionally, there will be functions to produce JSON data from database queries:
Here is a simple example that uses the new query_to_json function:
SELECT query_to_json('select user_name as username, role_name as role from USER',false);
This query will produce JSON data:
This could be very useful for web applications that use a lot of JSON data to communicate between the frontend and backend.