Tcl_script_map_pg_tables

https://www.gaia-gis.it/fossil/virtualpg/index

Definition

Language:	Tcl
Plugin for language:	ScriptingTcl
How to use:	Create custom SQL function. Suggested name: map_pg_tables
Function arguments	2, a PostgreSQL connection string, a name prefix for mapped tables (can be empty)
Function usage:	SELECT map_pg_tables('host=localhost port=5432 dbname=my_db user=my_user', '');
Description:	Maps all tables from public schema of provided PostgreSQL database to local SQLite tables (VIRTUAL tables), so you can user UPDATE/DELETE/SELECT/INSERT on them as on any other regular tables. You can pass empty string as second parameter to map tables with exactly same name as original, or you can pass for example pg to map PostgreSQL table my_data locally as pg_my_data. This function depends on VirtualPG SQLite extension, so you need to have it loaded.

Code

set SCHEMA "public"

if {$prefix != ""} {
    set prefix "${prefix}_"
}

db eval "
CREATE VIRTUAL TABLE _letos_tmp_pg_tables
        USING VirtualPostgres(
            $connStr,
            information_schema,
            tables
        )"

set tables [db eval "SELECT table_name FROM _letos_tmp_pg_tables WHERE table_schema = '$SCHEMA' ORDER BY table_name"]
foreach t $tables {
    db eval "
        CREATE VIRTUAL TABLE \"$prefix$t\"
            USING VirtualPostgres(
                $connStr,
                $SCHEMA,
                $t
            )"
}

db eval "DROP TABLE _letos_tmp_pg_tables"

return "Mapped [llength $tables] tables: [join $tables {, }]"