How to select columns that have name begining with same prefix ?

Question

Using postgreSQL 8.1.11, is there a way to select a set of columns that have name begining with same prefix.

Suppose we have columns : PREFIX_col1, PREFIX_col2, ...

Is it possible to do a request like :

SELECT 'PREFIX_*' FROM mytable;

Wich of course doesn't work.

Answer 1

You can diff the initial substrings find it in http://sourceforge.net/projects/pgdiff/

Answer 2

To me it looks like the syntax description of PostgreSQL 8.x's SELECT statement does not allow this. A SELECT list must be an expression or list of expressions, and the syntax for expressions does not seem to allow for wildcarded partial column names.

Share and enjoy.

Answer 3

You are going to have to construct the query with a query and use EXECUTE. Its a little easier w/ 8.3+. Here's a query that will run on 8.1 and pulls all columns starting with r% from the film table

$$
DECLARE 
   qry  TEXT;
BEGIN
    SELECT 'SELECT ' || substr(cols, 2, length(cols) - 2) ||
      ' FROM film' INTO qry
    FROM (
        SELECT array(
            SELECT quote_ident(column_name::text)
            FROM information_schema.columns 
            WHERE table_schema = 'public'
              AND table_name = 'film'
              AND column_name LIKE 'r%'
            ORDER BY ordinal_position
        )::text cols 
        -- CAST text so we can just strip off {}s and have column list
    ) sub;

    EXECUTE qry;
END;
$$