Revision 10302
Added by Aaron Marcuse-Kubitza over 11 years ago
| lib/sql_io.py | ||
|---|---|---|
| 376 | 376 |
INSERT ON DUPLICATE KEY UPDATE (which does not provide SELECT |
| 377 | 377 |
functionality), and PostgreSQL's INSERT RETURNING (which throws an error |
| 378 | 378 |
on duplicate instead of returning the existing row). |
| 379 |
However, PostgreSQL 9.1+ now provides a way to implement insert/on duplicate |
|
| 380 |
select just once for each table using the new INSTEAD OF triggers |
|
| 381 |
(http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html), |
|
| 382 |
which even support INSERT RETURNING. INSTEAD OF triggers can also be used to |
|
| 383 |
implement SQL functions without requiring special support by put_table(). |
|
| 384 |
(INSTEAD OF triggers were not used when put_table() was developed, because |
|
| 385 |
it was necessary to support PostgreSQL 9.0.) |
|
| 379 | 386 |
|
| 380 | 387 |
@param in_tables The main input table to select from, followed by a list of |
| 381 | 388 |
tables to join with it using the main input table's pkey |
Also available in: Unified diff
lib/sql_io.py: put_table(): documented that PostgreSQL 9.1+ now provides a way to implement insert/on duplicate select just once for each table (instead of dynamically for each insert) using the new INSTEAD OF triggers (http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html). INSTEAD OF triggers were not used when put_table() was developed, because it was necessary to support PostgreSQL 9.0, which was installed on the Mac and not easily upgradeable. it was eventually upgraded to add PostGIS, which required a complete reinstall of the DB from the staging tables, with the associated staging table reload bugs, as well as complete removal of the old Postgres version.