problem with TEMPORARY TABLE

Question

Within PHP I do:

1.) A temporary table is created: CREATE TEMP TABLE new_table AS SELECT .... FROM ...;

2.) AFter that I want to use this table to create a shape file: shell_exec ("pgsql2shp .... -u username -P password ...);

Separetly those two things work, but by creating a temporary table and after that using this table in pgsql2shp does not work. I pressume this is because temporary table duration is to the end of session. But to create shp file I need to use username and password what means new session starts and temporary table is dropped before I use it for shape creation.

Any tip how to solve it?

Thank you!

Answer 1

Yes, temporary tables are dropped at the end of the session or optionally at the end of the transaction. In general you cannot pass them to another process.

Create a real table, give it a unique name by sticking an id to it and drop the table after running the shape creation.

If you cannot change the pgsql2shp program, you might wrap it in a script and call that instead. However you should be able to pass the name of the table to the pgsql2shp program.

Can't you run the query in the pgsql2shp program?

As an alternative, the humble flatfile in the /tmp folder can also work very well.