drop server if exists fdw_utilities cascade;

-- FUNCTION: public.create_fdw_utilities(text, text, text, text)

-- DROP FUNCTION public.create_fdw_utilities(text, text, text, text);

CREATE OR REPLACE FUNCTION public.create_fdw_utilities(
	this_host_address text,
	this_database_name text,
	this_user_name text,
	this_password text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER 
    ROWS 0
AS $BODY$

declare
	sql_stmt	text;
        port_number text;
        this_table_name text;

begin

    port_number = '5432';
    
    drop server if exists fdw_utilities cascade;
    
    sql_stmt = 	   'create server fdw_utilities '
				|| ' foreign data wrapper postgres_fdw '
				|| ' options (host'
                                || ''''
                                || this_host_address
                                || ''''
                                || ', dbname '
				|| ''''
				|| this_database_name                
                                || ''''
                                || ', port'
                                || ''''
                                || port_number
				|| ''''
                                || ')';
                
    execute sql_stmt;
    
    
    sql_stmt = 	'create user mapping for public '
    			|| ' server fdw_utilities '
                        || ' options (user '
                        || ''''
                        || this_user_name
                	|| ''''
                        || ', password '
                        || ''''
                        || this_password
                        || ''''
                        || ')';
                
    execute sql_stmt;
    
    create foreign table "rubin_permissions" (
		pg_user_name		text,
               permissions			text,
      	       db_name				text,
    	       pg_server_name		text,
    	       pg_server_address  	text
    ) server fdw_utilitiesALTER FUNCTION public.create_fdw_utilities(text, text, text, text)
    OWNER TO rubin;
        options (
            schema_name 'public',
            table_name 'rubin_permissions'
        );
 	
	sql_stmt =     'create view public.user_rubin_permissions as select '
    			|| ' * from rubin_permissions where pg_user_name = '
                        || ''''
                        || this_user_name
                 	|| '''';          
       
    execute sql_stmt;

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

    sql_stmt =      'grant usage on schema public to ' 
                || this_user_name;

    execute sql_stmt;

  
    sql_stmt =      'grant all on public.user_rubin_permissions to ' 
                || this_user_name;

    execute sql_stmt;

end 
ALTER FUNCTION public.create_fdw_utilities(text, text, text, text)
    OWNER TO rubin;
$BODY$;

    drop server if exists fdw_utilities cascade;

select
         substring(input_line, char_length(substring(input_line from '[0-9.]*[ ]')) + 1) as node_name
        ,text2ltree(substring(input_line from '[0-9.]*')) as node_path
from staging;

psql:load.sql:4: ERROR:  syntax error
DETAIL:  Unexpected end of line.