Revision 11033
Added by Aaron Marcuse-Kubitza about 11 years ago
sql_io.py | ||
---|---|---|
372 | 372 |
|
373 | 373 |
Note that much of the complexity of the normalizing algorithm is due to |
374 | 374 |
PostgreSQL (and other DB systems) not having a native command for |
375 |
insert/on duplicate select. This operation is a cross between MySQL's |
|
376 |
INSERT ON DUPLICATE KEY UPDATE (which does not provide SELECT |
|
377 |
functionality), and PostgreSQL's INSERT RETURNING (which throws an error |
|
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.) |
|
375 |
INSERT ON DUPLICATE SELECT (wiki.vegpath.org/INSERT_ON_DUPLICATE_SELECT). |
|
376 |
For PostgreSQL 9.1+, this can now be emulated using INSTEAD OF triggers. |
|
377 |
For earlier versions, you instead have to use this function. |
|
386 | 378 |
|
387 | 379 |
@param in_tables The main input table to select from, followed by a list of |
388 | 380 |
tables to join with it using the main input table's pkey |
Also available in: Unified diff
lib/sql_io.py: put_table(): added link to new INSERT ON DUPLICATE SELECT wiki page, which now contains the explanation in the doc comment