0
create table foo (i int, j text);

select table_schema, table_name, column_name , ordinal_position 
from information_schema.columns where table_name = 'foo';

Output

table_schema   table_name  column_name  ordinal_position
public         foo             i             1 
public         foo             j             2

By using second statement I need to form a select query select i, j from public.foo. A simple function would suffice where if we pass a table name the result can be the string of select statement

user1720827
  • 137
  • 1
  • 3
  • 15

1 Answers1

0

You can use aggregation:

select concat_ws(' ',
    'select',
    string_agg(column_name, ', ' order by ordinal_position),
    'from',
    table_schema || '.' || table_name
) q
from information_schema.columns 
where table_name = 'foo'
group by table_schema, table_name

Or without a group by clause:

select concat_ws(' ',
    'select',
    string_agg(column_name, ', ' order by ordinal_position),
    'from',
    max(table_schema) || '.' || max(table_name)
) q
from information_schema.columns 
where table_name = 'foo' and table_schema = 'public'

Demo on DB Fiddle:

| q                           |
| :-------------------------- |
| select i, j from public.foo |
GMB
  • 216,147
  • 25
  • 84
  • 135