FEAT: Support `CREATE TEMP TABLE <pg_db>.<pg_schema>.temp_table`

[NickCrews]

If I have a postgres server attached at my_pg_db, I want to be able to do CREATE TEMP TABLE my_pg_db.public.tmp_table AS SELECT my_col from my_pg_db.public.original, and have this create a temp table on the postgres side. Currently this results in ParserException: Parser Error: TEMPORARY table names can *only* use the "temp" catalog. IDK, if this actually is a parser exception, then this might be out of control of this extension, and duckdb itself would have to change it's parser. If you have a suggestion for a workaround in the meantime to create temp tables on the postgres side, that would be awesome. Thanks!

[lambeolino]

Hi I am having the same problem, because I want to create a temp table on postgres from parquet files, do you found a solution for this @NickCrews ? :)

[NickCrews]

I have found no workaround, but I haven't worked on this since I filed the issue, so maybe there is some workaround.

[lambeolino]

okay thanks I'll try something another than!

[NickCrews]

I actually just had an idea, but haven't tested.

I think you could create it on the postgres side using eg ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres); CALL postgres_execute('postgres_db', 'CREATE TEMPORARY TABLE my_table (i INTEGER)');

See https://duckdb.org/docs/stable/core_extensions/postgres.html

[staticlibs]

Hi, thanks for raising the issue! I don't believe this can work in general due to scanner using multiple connections to Postgres from a single DuckDB connection. See more details in #451. So I'll close this as not planned.