PostgreSQL – FDW – Generació scripts

Com hen vist en el post anterior, PostgreSQL – Foreign Data Wrapper, per tal de configurar el FDW cal generar els scripts de les taules. Amb aquesta consulta es pot generar l’script de creació de totes les taules d’una base de dades PostgreSQL.

SELECT case when ordinal_position=1 then concat('drop foreign table if exists ',c.table_schema,'_' ,c.table_name,';') else '' end AS strdrop,
case when ordinal_position=1 then concat('create FOREIGN TABLE ',c.table_schema,'_' ,c.table_name,'(') else '' end ||
 concat(
    case when ordinal_position=1 then '' else ',' end,
   '"',column_name,'"'
    ' ',
    case udt_name
        when 'varchar' then
            case
                when character_maximum_length is not null then concat(udt_name,'(',character_maximum_length,')')
                else udt_name
            end
        when 'numeric' then
            case
                when numeric_precision is not null  then concat(udt_name,'(',numeric_precision,',' ,numeric_scale,')' )
                else udt_name
            end
        else udt_name
    end,
    case when is_nullable='NO' then ' NOT NULL' else '' end
)
||
case when ordinal_position=ma.mo then concat(')SERVER <nom_server_fdw> OPTIONS (schema_name ''',c.table_schema,''', table_name ''',c.table_name,''');')  else '' end as strend
  FROM information_schema.columns c
    join (
      select table_catalog, table_schema, table_name, max(ordinal_position) mo
        FROM information_schema.columns
        group by table_catalog, table_schema, table_name
    ) ma on c.table_catalog =ma.table_catalog and c.table_schema =ma.table_schema and c.table_name =ma.table_name
  where c.table_schema ='public'
  order by c.table_catalog , c.table_schema , c.table_name, c.ordinal_position

D’aquest script cal modificar <nom_server_fdw> amb el nom del servidor de FDW. El nom de la taula creada serà <nom_schema>_<nom_taula>.

L’script es basa en la taula de metadata information_schema.columns que conté informació sobre totes les taules i totes les columnes d’una base de dades. En cas de ser la 1a columna (ordinal_position=1), es genera la sentència de drop i la de create. Per a les columnes que no son la primera es separa cada columna amb una coma (‘,’). A la darrera columna (camp mo de la subconsulta) es genera la part final de configuració del server.
D’aquesta forma podem generar els scripts de totes les taules sense esforç de forma automàtica.

Amb el següent consulta es poden generar els scripts si l’origen és una bbdd MySQL/Mariadb.

SELECT case when ordinal_position=1 then concat('drop foreign table if exists ',c.table_schema,'_' ,c.table_name,';') else '' end AS strdrop,
concat(case when ordinal_position=1 then concat('create FOREIGN TABLE ',c.table_schema,'_' ,c.table_name, '(') else '' end,
concat(
case when ordinal_position=1 then '' else ',' end,
'"',column_name,'"'
' ',
case when data_type like  '%int' then data_type
when data_type='enum' then 'varchar(500) '
when data_type='datetime' then 'timestamp '
when data_type='longtext' then 'varchar(4000) '
when data_type='mediumtext' then 'varchar(4000) '
when data_type='tinytext' then 'varchar(4000) '
when data_type='tinyint' then 'int '
when data_type='mediumint' then 'int '
when data_type='double' then 'float '
else column_type
end,
case when is_nullable='NO' then ' NOT NULL' else '' end
)
,case when ordinal_position=ma.mo then concat(')SERVER  <nom_server_fdw> OPTIONS (dbname ''',c.table_schema,''', table_name ''',c.table_name,''');')  else '' end) as strcreate
FROM information_schema.columns c
join (
select table_catalog, table_schema, table_name, max(ordinal_position) mo
FROM information_schema.columns
group by table_catalog, table_schema, table_name
) ma on c.table_catalog =ma.table_catalog and c.table_schema =ma.table_schema and c.table_name =ma.table_name
where c.table_schema ='wordpress'
order by c.table_catalog , c.table_schema , c.table_name, c.ordinal_position

L’script es basa en la taula de metadata information_schema.columns que conté informació sobre totes les taules i totes les columnes d’una base de dades. En cas de ser la 1a columna (ordinal_position=1), es genera la sentència de drop i la de create. Per a les columnes que no son la primera es separa cada columna amb una coma (‘,’). A la darrera columna (camp mo de la subconsulta) es genera la part final de configuració del server.
Cal tenir en compte que els tipus de dades de MySQL/Mariadb i PostgreSQL no són iguals i que cal aplicar una transofrmació de tipus de la columna column_type.