Revision 3009
Added by Aaron Marcuse-Kubitza over 12 years ago
| lib/sql.py | ||
|---|---|---|
| 621 | 621 |
log_level=log_level) |
| 622 | 622 |
|
| 623 | 623 |
def mk_insert_select(db, table, cols=None, select_query=None, returning=None, |
| 624 |
embeddable=False): |
|
| 624 |
embeddable=False, ignore=False):
|
|
| 625 | 625 |
''' |
| 626 | 626 |
@param returning str|None An inserted column (such as pkey) to return |
| 627 | 627 |
@param embeddable Whether the query should be embeddable as a nested SELECT. |
| 628 | 628 |
Warning: If you set this and cacheable=True when the query is run, the |
| 629 | 629 |
query will be fully cached, not just if it raises an exception. |
| 630 |
@param ignore Whether to ignore duplicate keys. |
|
| 630 | 631 |
''' |
| 631 | 632 |
table = sql_gen.remove_table_rename(sql_gen.as_Table(table)) |
| 632 | 633 |
if cols == []: cols = None # no cols (all defaults) = unknown col names |
| ... | ... | |
| 635 | 636 |
if select_query == None: select_query = 'DEFAULT VALUES' |
| 636 | 637 |
if returning != None: returning = sql_gen.as_Col(returning, table) |
| 637 | 638 |
|
| 638 |
# Build query |
|
| 639 | 639 |
first_line = 'INSERT INTO '+table.to_str(db) |
| 640 |
query = first_line |
|
| 641 |
if cols != None: query += '\n('+', '.join(cols)+')'
|
|
| 642 |
query += '\n'+select_query |
|
| 643 | 640 |
|
| 644 |
if returning != None: |
|
| 645 |
query += '\nRETURNING '+sql_gen.to_name_only_col(returning).to_str(db) |
|
| 641 |
def mk_insert(select_query): |
|
| 642 |
query = first_line |
|
| 643 |
if cols != None: query += '\n('+', '.join(cols)+')'
|
|
| 644 |
query += '\n'+select_query |
|
| 645 |
|
|
| 646 |
if returning != None: |
|
| 647 |
returning_name_col = sql_gen.to_name_only_col(returning) |
|
| 648 |
query += '\nRETURNING '+returning_name_col.to_str(db) |
|
| 649 |
|
|
| 650 |
return query |
|
| 646 | 651 |
|
| 652 |
lang = 'sql' |
|
| 653 |
if ignore: |
|
| 654 |
embeddable = True # must use function |
|
| 655 |
lang = 'plpgsql' |
|
| 656 |
|
|
| 657 |
query = '''\ |
|
| 658 |
BEGIN |
|
| 659 |
END; |
|
| 660 |
''' |
|
| 661 |
else: query = mk_insert(select_query) |
|
| 662 |
|
|
| 647 | 663 |
if embeddable: |
| 648 |
assert returning != None |
|
| 664 |
return_type = 'void' |
|
| 665 |
if returning != None: |
|
| 666 |
return_type = 'SETOF '+returning.to_str(db)+'%TYPE' |
|
| 649 | 667 |
|
| 650 | 668 |
# Create function |
| 651 | 669 |
function_name = sql_gen.clean_name(first_line) |
| 652 |
return_type = 'SETOF '+returning.to_str(db)+'%TYPE' |
|
| 653 | 670 |
while True: |
| 654 | 671 |
try: |
| 655 | 672 |
function = db.TempFunction(function_name) |
| ... | ... | |
| 657 | 674 |
function_query = '''\ |
| 658 | 675 |
CREATE FUNCTION '''+function.to_str(db)+'''() |
| 659 | 676 |
RETURNS '''+return_type+''' |
| 660 |
LANGUAGE sql
|
|
| 677 |
LANGUAGE '''+lang+'''
|
|
| 661 | 678 |
AS $$ |
| 662 |
'''+query+''';
|
|
| 679 |
'''+query+''' |
|
| 663 | 680 |
$$; |
| 664 | 681 |
''' |
| 665 | 682 |
run_query(db, function_query, recover=True, cacheable=True, |
| ... | ... | |
| 670 | 687 |
# try again with next version of name |
| 671 | 688 |
|
| 672 | 689 |
# Return query that uses function |
| 690 |
cols = None |
|
| 691 |
if returning != None: cols = [returning] |
|
| 673 | 692 |
func_table = sql_gen.NamedTable('f', sql_gen.FunctionCall(function),
|
| 674 |
[returning]) # AS clause requires function alias
|
|
| 693 |
cols) # AS clause requires function alias
|
|
| 675 | 694 |
return mk_select(db, func_table, start=0, order_by=None) |
| 676 | 695 |
|
| 677 | 696 |
return query |
Also available in: Unified diff
sql.py: mk_insert_select(): Added ignore param to do an INSERT IGNORE equivalent operation. Added basic structure for INSERT IGNORE. Support embeddable queries without RETURNING columns (which is the case for most INSERT IGNORE queries).