Get All Column Names of Table In Postgres 9

If you need to get a list of the column names in a table in Postgres, here's a line of REALbasic code with the SQL SELECT statement:

sql = "SELECT attname, attnum FROM pg_attribute, pg_class WHERE attrelid = pg_class.oid AND relname = " + DBUtils.escapeAndAddQuotes(tableName) + " AND attnum > 0 ;"

You can see that a join is required across two tables of Postgres' meta-data. I've not studied Postgres' meta-data tables, but worked out this code with some googling and experimenting.

The call to DBUtils.escapeAndAddQuotes is unrelated. That is my own method for 3 things:

• Verify the string is in UTF-8 encoding.
• Replace any occurrence of a single quotation mark (Unicode APOSTROPHE-QUOTE) with two such characters, to make the string safe for SQL execution.
• Add single quote marks around the string for use as a literal in the SQL execution.

An alternative approach suggested by others is this:
"select * from tablename where 0=1"
Obviously that is a hack, though it may be an efficient and effective one. Instead I chose the other approach.