Selecting columns whose name matches a regular expression in PostgreSQL

Question

How can I select only those columns whose name matches a regular expression in PostgreSQL?

For example, how do I select only the columns whose name begins with 'A' in the following table, without explicitly enumerating them in the select list?

id  A1 A2 A3 A4 A5 B
1   a  b  c  d  e  f
2   g  h  i  j  k  l

Answer 1

You will need to write a dynamic sql('select '||colname||' from (yourtable)') to accomplish this and dynamic sql should have supplied column names from the following sql:

select column_name from information_schema.columns where table_name=(your table) and column_name like 'a%';