Code Snippets

Shell Script To Configure A Read Only User On A PostgreSQL Database

**UPDATE: New article here.

Its a little more complicated to create a read only database user with PostgreSQL than say using something like MySQL. You will need to grant select access on each table in the database for the read only user. Keep in mind that each time you add a new table you will need to either run the shell script below again or manually grant select access on the new table for the read only user.

Code Snippets

Writing a function for frequently used SQL (returning custom columns) in PostgreSQL

There’s no real easy way to do this, but I’d like to write a database function (in this case a PL/pgSQL function) to run a SQL SELECT statement with custom columns in the output.

First, you need to create a custom data type for the custom columns (apparently, this step won’t be necessary when PostgreSQL 8.4 supports the RETURNS TABLE syntax in functions).  For example