CREATE SCHEMA IF NOT EXISTS :schema;
SET search_path to :schema, public;

---create view
CREATE OR REPLACE VIEW :schema.vw_relations_coord AS
SELECT rel.relation_id, en_to.entity_name AS en_to_name, en_to.entity_lat AS en_to_lat, en_to.entity_lon AS en_to_lon, en_from.entity_name AS en_from_name, en_from.entity_lat AS en_from_lat, en_from.entity_lon AS en_from_lon FROM tbl_relations AS rel JOIN tbl_entities AS en_to ON rel.relation_to_entity = en_to.entity_id JOIN tbl_entities AS en_from ON rel.relation_from_entity = en_from.entity_id;

---create table
CREATE TABLE IF NOT EXISTS :schema.tbl_relations_coord AS SELECT * FROM :schema.vw_relations_coord;
