A foreign table server

A customer of mine who is by the way a good programmer has often original ideas.

I develop for him front ends written in Access/VBA and back ends written in Postgresql.

Specifications
The front end connects with a database called the sandbox because each user has its own private database.

This sandbox will import tables from other tables with Foreign Data Wrapper mechanism.

This sandbox has also its own tables.

The user can choose at login the databases and the tables.

Several databases have the same structure but have different data.

A same table can thus come from different databases.

Some applications need always the same set of tables.

Solution

1. The kernel

The front end sends at login the following instruction :

select public.connect_kernel(host ip adress, username, password)

and the following function is called on the back end :

CREATE OR REPLACE FUNCTION public.connect_kernel(
   this_host_address text,
   this_user_name text,
   this_password text,
   this_port_number text DEFAULT '5432'
   )
   RETURNS boolean
   LANGUAGE 'plpgsql'
   COST 100
   VOLATILE SECURITY DEFINER
AS $BODY$
declare
   sql_stmt text;
   fdw_server_exists boolean;
begin

   sql_stmt =    'select public.database_is_connected('
              || ''''
              || 'kernel'
              || ''''
              || ');';

    execute sql_stmt into fdw_server_exists;

    if fdw_server_exists is true then
      return true;
    end if;

    sql_stmt =    'create server '
               || 'fdw_kernel'
               || ' foreign data wrapper postgres_fdw '
               || ' options (host '
               || ''''
               || this_host_address
               || ''''
               || ', dbname '
               || ''''
               || 'kernel'
               || ''''
               || ', port '
               || ''''
               || this_port_number
               || ''''
               || ')';

    execute sql_stmt;

    sql_stmt =    'create user mapping for public '
               || ' server '
               || 'fdw_kernel'
               || ' options (user '
               || ''''
               || this_user_name
               || ''''
               || ', password '
               || ''''
               || this_password
               || ''''
               || ')';

    execute sql_stmt;

    sql_stmt =    'grant usage on foreign server fdw_kernel to '
               || this_user_name;

    execute sql_stmt;

    create foreign table "permissions_matrix" (
        pg_user_name text,
        pg_permission text,
        pg_database_name text,
        pg_server_name text,
        pg_server_address text,
        pg_port_number text
     ) server fdw_kernel
     options (
        schema_name 'public',
        table_name 'permissions_matrix'
     );

     create foreign table "sdbx_tables_2_link" (
        database_type text, 
        front_end_table_name text,
        pg_table_name text, 
        pg_pk text
     ) server fdw_kernel
     options (
         schema_name 'public',
         table_name 'sdbx_tables_2_link'
     );

     return true;

     exception when others then
         perform public.write2log(
            'ERROR : connect_kernel : '
          || sql_stmt
          || ', '
          || sqlerrm
        );
        return false;

end $BODY$;

2. The foreign tables definition

CREATE OR REPLACE FUNCTION public.get_fdw_table_definition(
   this_schema_name text,
   this_table_name text
)
   RETURNS text
   LANGUAGE 'sql'
   COST 100
   VOLATILE 
AS $BODY$

  WITH cols AS 
   ( SELECT 
    	 cl.relname As table_name
    	,na.nspname As table_schema
    	,att.attname As column_name
    	,format_type(ty.oid,att.atttypmod) AS column_type
    	,attnum As ordinal_position
    FROM 
    	pg_attribute att
    JOIN pg_type ty 
    	ON ty.oid=atttypid
    JOIN pg_namespace tn 
    	ON tn.oid=ty.typnamespace
    JOIN pg_class cl 
    	ON cl.oid=att.attrelid
    JOIN pg_namespace na 
    	ON na.oid=cl.relnamespace
    LEFT OUTER JOIN pg_type et 
    	ON et.oid=ty.typelem
    LEFT OUTER JOIN pg_attrdef def 
        ON adrelid=att.attrelid AND adnum=att.attnum
    WHERE 
     	cl.relkind IN('v','r') 
    AND
    	na.nspname = this_schema_name 
    AND 
    	cl.relname = this_table_name 
    AND 
        att.attnum > 0
    AND 
        NOT att.attisdropped 
    ORDER BY
        att.attnum 
)        
SELECT 
      'CREATE FOREIGN TABLE "THIS_TO_SCHEMA_NAME"."THIS_TO_TABLE_NAME" ('
   || string_agg(quote_ident(column_name) 
   || ' ' 
   || column_type,  ', ' ORDER BY ordinal_position)
   || ')  SERVER THIS_FDW_SERVER_NAME OPTIONS (schema_name '
   || 'QUOTE'
   || 'THIS_FROM_SCHEMA_NAME'
   || 'QUOTE'
   || ','
   || 'table_name'
   || ' QUOTE' 
   || table_name
   || 'QUOTE'
   || '); '
from
   cols     
GROUP BY 
   table_schema, 
   table_name

$BODY$;
CREATE OR REPLACE VIEW public.fdw_table_definitions AS 
SELECT 
   tables.table_schema AS schema_name,
   tables.table_name,
   get_fdw_table_definition(
      tables.table_schema, 
      tables.table_name
   ) AS fdw_table_definition
FROM 
    information_schema.tables
WHERE 
   tables.table_schema != 'pg_catalog' 
AND 
   tables.table_schema != 'information_schema';


 

CREATE OR REPLACE FUNCTION public.connect_database(
   this_database_name text,
   this_user_name text,
   this_password text
)
   RETURNS boolean
   LANGUAGE 'plpgsql'
   COST 100
   VOLATILE SECURITY DEFINER 
AS $BODY$
declare
   sql_stmt text;
   fdw_server_exists boolean;
   this_fdw_server_name text;
   this_host_address text;
   this_port_number text;
begin

    sql_stmt =     'select pg_server_address, pg_port_number '
   		|| 'from public.get_database_parameters('
                || ''''
                || this_database_name
                || ''''
                || ','
                || ''''
                || this_user_name
                || '''' 
                || ')';

    execute sql_stmt into this_host_address, this_port_number;
    
    this_fdw_server_name = 'fdw_' || this_database_name;
    
    --
    -- test if this fdw server for this database exists
    --
    sql_stmt =     'select public.database_is_connected('
                || ''''
                || this_database_name
                || ''''
                || ');';

    execute sql_stmt into fdw_server_exists;

    if fdw_server_exists is true then
        return true;
    end if;

    sql_stmt = 	   'create server '
   		|| this_fdw_server_name
		|| ' foreign data wrapper postgres_fdw '
		|| ' options (host '
                || ''''
                || this_host_address
                || ''''
                || ', dbname '
		|| ''''
		|| this_database_name                
                || ''''
                || ', port '
                || ''''
                || this_port_number
		|| ''''
                || ')';
                
    execute sql_stmt;
    
    
    sql_stmt = 	   'create user mapping for public '
   		|| ' server '
                || this_fdw_server_name
                || ' options (user '
                || ''''
                || this_user_name
               	|| ''''
                || ', password '
                || ''''
                || this_password
                || ''''
                || ')';
                
    execute sql_stmt;
    
    
    sql_stmt =     'grant usage on foreign server '
   		|| this_fdw_server_name
                || ' to '
                || this_user_name;
                
    execute sql_stmt;
     
    
    sql_stmt =     'create foreign table ' 
            	|| this_database_name
       		|| '_'
            	|| 'fdw_table_definitions'
            	|| ' ('
            	|| 'schema_name text,'
            	|| 'table_name text,'
            	|| 'fdw_table_definition text'
            	|| ') server '
            	|| this_fdw_server_name
            	|| ' options ('
            	|| ' schema_name '
            	|| ''''
            	|| 'public'
            	|| ''''
            	|| ','
            	|| ' table_name '
            	|| ''''
            	|| 'fdw_table_definitions'
            	|| ''''
            	|| ')';

    execute sql_stmt;

    return true;
    
     exception when others then 
        perform public.write2log(
	      'ERROR : connect_database : '  
           || sql_stmt 
           || ', ' 
           || sqlerrm
    	   );
	return false;
end 

$BODY$;

4. The attach table and attach schema functions

CREATE OR REPLACE FUNCTION public.attach_table(
  this_database_name text,
  this_from_table_name text,
  this_to_table_name text DEFAULT NULL,
  this_from_schema_name text DEFAULT 'public',
  this_to_schema_name text DEFAULT 'public'
  )
  RETURNS boolean
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE 
AS $BODY$

declare
   this_fdw_server_name text;
   this_fdw_table_definition text;
   sql_stmt text;
   fdw_server_exists boolean;
begin

   sql_stmt =     'select public.database_is_connected('
               || ''''
               || this_database_name
               || ''''
               || ');';

    execute sql_stmt into fdw_server_exists;
    
    if fdw_server_exists is false then
       perform public.write2log(
             'ERROR : attach_table : '  
          || this_database_name
          || ' is not connected'
   	 );
	return false;
    end if;
    
    if this_to_table_name is null then
    	this_to_table_name = this_from_table_name;
    end if;
    
    this_fdw_server_name = 'fdw_' || this_database_name;
        
    sql_stmt =     'select fdw_table_definition from '
                || this_database_name
                || '_'
                || 'fdw_table_definitions'
                || ' where table_name = '
                || ''''
                || this_to_table_name
                || ''''
                || ' and schema_name = '
                || ''''
                || this_from_schema_name
                || ''''
                || ';';

   execute sql_stmt into this_fdw_table_definition;
    
   if this_fdw_table_definition is null then
      perform public.write2log(
            'ERROR : attach_table : no fdw definition for table '  
         || this_from_table_name 
      );
      return false;
   end if;
 
    
   select replace(
      this_fdw_table_definition,
      'THIS_FDW_SERVER_NAME', 
      this_fdw_server_name
   ) into this_fdw_table_definition;
    
   select replace(
      this_fdw_table_definition, 
      'THIS_TO_TABLE_NAME', 
      this_to_table_name
   ) into this_fdw_table_definition;
    
   select replace(
      this_fdw_table_definition, 
      'QUOTE', 
      ''''
   ) into this_fdw_table_definition;
        
   select replace(
      this_fdw_table_definition, 
      'THIS_FROM_SCHEMA_NAME', 
      this_from_schema_name
   ) into this_fdw_table_definition;
     
    select replace(
       this_fdw_table_definition, 
       'THIS_TO_SCHEMA_NAME', 
       this_to_schema_name
    ) into this_fdw_table_definition;
    
    execute this_fdw_table_definition;
    
    return true;
    
    exception when others then 
       perform public.write2log(
             'ERROR : attach_table : '  
          || this_fdw_table_definition 
          || ', '
          || sqlerrm
    	);
	return false;
end $BODY$;

and the attach_schema :

CREATE OR REPLACE FUNCTION public.attach_schema(
   this_database_name text,
   this_from_schema_name text DEFAULT 'public',
   this_to_schema_name text DEFAULT 'public'
   )
   RETURNS boolean
   LANGUAGE 'plpgsql'
   COST 100
   VOLATILE 
AS $BODY$
declare
   sql_stmt text;
   fdw_server_exists boolean;
begin

   sql_stmt =    'select public.database_is_connected('
              || ''''
              || this_database_name
              || ''''
              || ');';

   execute sql_stmt into fdw_server_exists;
    
   if fdw_server_exists is false then
      perform public.write2log(
            'ERROR : attach_schema : '  
         || this_database_name
         || ' is not connected'
      );
      return false;
    end if;
    
   sql_stmt =	   'import foreign schema '
   	       || this_from_schema_name
               || ' from server '
    	       || 'fdw_'
               || this_database_name
    	       || ' into '
               || this_to_schema_name;
                
   execute sql_stmt;
   
   return true;    
   
   exception when others then 
      perform public.write2log(
            'ERROR : attach_schema : '  
         || sql_stmt 
     	 || ', '
       	 || sqlerrm
      );
      return false;
end $BODY$;
CREATE OR REPLACE FUNCTION public.database_is_connected(
   this_database_name text
   )
   RETURNS boolean
   LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

declare
   r 	text;
begin
   select 
      srvname
   into
      r
   from 
      pg_foreign_server
   join 
      pg_foreign_data_wrapper w 
   on 
      w.oid = srvfdw
   where
      srvname = 'fdw_' || this_database_name;
        
    if r is not null then 
        return true; 
    else 
        return false; 
    end if;
  
end $BODY$;
CREATE OR REPLACE FUNCTION public.list_fdw_servers(
   )
   RETURNS SETOF record 
   LANGUAGE 'sql'
   COST 100
   VOLATILE 
AS $BODY$
   select 
      srvname as name, 
      srvowner::regrole as owner, 
      fdwname as wrapper, 
      srvoptions as options
   from 
       pg_foreign_server
   join 
       pg_foreign_data_wrapper w 
   on 
       w.oid = srvfdw;

$BODY$;
CREATE OR REPLACE FUNCTION public.get_database_parameters(
	this_database_name text,
	this_username text,
	OUT pg_server_address text,
	OUT pg_port_number text)
    RETURNS record
    LANGUAGE 'sql'
    COST 100
    VOLATILE 
AS $BODY$

   select 
      pg_server_address,
      pg_port_number
   from
      permissions_matrix
   where
      pg_database_name = this_database_name
   and
      pg_user_name = this_username;
        
$BODY$;