New Feature: creating TEMP tables in PostgreSQL

[lotharschulz]

fix: #55

Proposed changes are:

• Parameter Adaptation: Introduced a new parameter temp in the function signature to indicate whether a temporary table should be created.

• Version Check for Temporary Table Support: Since temporary tables are supported in PostgreSQL version 7.1 and above, a version check is added similar to the existing one to ensure the PostgreSQL version is 7.1 or higher to support temporary tables.

• Modified SQL Statement for Temporary Table: If the temp parameter is true, prepend TEMPORARY to the CREATE TABLE or CREATE TABLE IF NOT EXISTS statement, depending on the PostgreSQL version.

• Adjusted the create Function Signature: Modified the function signature to include the temp parameter with a default value of false to maintain backward compatibility.

• Update SQL Construction Logic: Incorporate the logic for adding TEMPORARY into the SQL statement construction based on the temp parameter and PostgreSQL version.

• Documentation: added comprehensive code inline comments and preserved stackoverflow reference

• Tests: Adapted the related test. Test now test 3 table creation cases

  • without optional create function parameter
  • with optional create function parameter default value
  • with optional create function parameter value: bool $temp = true

Puzzler: given this repository, I could not find functions that call the create function. I assume these functions could be also modified so we - as Keboola customers (as well as all other customers) - can benefit from the new/missing functionality.

The create table documentation reads:

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). The default search_path includes the temporary schema first and so identically named existing permanent tables are not chosen for new plans while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

I could not find documentation about the session context. I just assume in this PR, that https://github.com/keboola/db-writer-pgsql/blob/master/src/Writer/Pgsql.php#L163 does not close a session, thats why the adapted tests include: https://github.com/keboola/db-writer-pgsql/commit/aec5105fe4a547124f7fc7551504b6dcf845fe08#diff-81aaf09773b0b78a851ec6b56a65f84bd2d7c93988d368bad0ceec860dc9251bR121

[lotharschulz]

I close this in favour of https://github.com/keboola/db-writer-pgsql/pull/54