I am trying to create tables from all the tables that have been imported into my foreign tables via import foreign schema. The reason I want to do this is simply to copy data from one PostgreSQL server database to another. I know that I won't get sequences and other table-related indexes, but that's alright. I don't wanna do a pg_dump, so I've been trying to do it this way, but so far I haven't had any luck with the below code.
When I run the function, the query is running, but the tables don't seem to start being created in my database and I am having a hard time figuring out if my function is actually working or not.
DROP FUNCTION gv.create_tables_from_foreign_schema(character varying,character varying);
CREATE OR REPLACE FUNCTION gv.create_tables_from_foreign_schema(_foreign_schema character varying, _local_schema character varying)
RETURNS void AS
$BODY$
declare
selectrow record;
begin
for selectrow in
select 'CREATE TABLE ' || quote_ident(_local_schema) || '.' ||quote_ident(t.table_name) || ' AS SELECT * FROM ' || quote_ident(_foreign_schema) || '.' ||quote_ident(t.table_name) || '' AS qry
from (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = _foreign_schema
)t
loop
execute selectrow.qry;
end loop;
end;
$BODY$
LANGUAGE plpgsql
SELECT gv.create_tables_from_foreign_schema('gv_import_fra_pgv_2018a', 'gv')
Does the function make sense to you?
