dynamic sql - Using query to set the column type in PostgreSQL -

after excellent answer alexandre guidet, attempted run following query:

 create table egg (id (select    pg_catalog.format_type(a.atttypid, a.atttypmod) datatype       pg_catalog.pg_attribute         a.attnum > 0    , not a.attisdropped    , a.attrelid = (      select c.oid      pg_catalog.pg_class c      left join pg_catalog.pg_namespace n on n.oid = c.relnamespace      c.relname ~ '^(tablename)$'     , pg_catalog.pg_table_is_visible(c.oid)    )    , a.attname = 'columnname')); 

postgresql, however, complains incorrect syntax. says cannot write: create table egg (id (select.
there workarounds? can't convert result of query text , reuse query?

there much simpler way that.

select pg_typeof(col)::text tbl limit 1 

only precondition template table holds at least 1 row. see manual on pg_typeof()

as milen wrote, need execute dynamic ddl statements this.
simpler do statement:

do $$begin execute 'create table egg (id '          || (select pg_typeof(col)::text tbl limit 1) || ')'; end$$; 

or, if not sure template table has rows:

do $$begin execute (    select format('create table egg (id %s)'                , format_type(atttypid, atttypmod))      pg_catalog.pg_attribute     attrelid = 'tbl'::regclass  -- name of template table    ,    attname = 'col'             -- name of template column    ,    attnum > 0 , not attisdropped    ); end$$; 

these conditions seem redundant, since specific column any

format() requires postgres 9.1+.

related:

• how check if table exists in given schema