Thread: PLPython function and multiple line insert
Hi everyone, I hope, I am on the right list. If not my apology. I'd like to insert multiple lines as following with PLPython: SQL statement: INSET INTO table1 (field1, field2, field3) VALES ('abc', 'abc', TRUE), ('def', 'def', FALSE), ('ghi', 'ghi', TRUE); Here the proposed function: CREATE OR REPLACE FUNCTION insert_rows() RETURNS boolean AS $$ . . . sql = "("INSERT INTO table1 (field1, field2, field3) VALUES ('abc', 'abc', TRUE), ('def', 'def', FALSE), ('ghi', 'ghi', TRUE");" pypl.execute(sql) . . $$ LANGUAGE 'plpythonu' VOLATILE I ran into trouble with quoting this SQL statement in PLPython (PostgreSQL 8.3.7). Any ideas/suggestions how to quote it either with pypl.execute() or pypl.plan()? Thanks for any suggestions and help in advance, Andi
Andi Klapper wrote: > > sql = "("INSERT INTO table1 (field1, field2, field3) > VALUES ('abc', 'abc', TRUE), > ('def', 'def', FALSE), > ('ghi', 'ghi', TRUE");" > pypl.execute(sql) > . > . > $$ > LANGUAGE 'plpythonu' VOLATILE > > I ran into trouble with quoting this SQL statement in PLPython > (PostgreSQL 8.3.7). Any ideas/suggestions how to quote it either with > pypl.execute() or pypl.plan()? The statement looks incorrect from the python point of view (not just plpython). You should quote multi-lined strings, or use an escape character (\) like: """line 1 line 2""" '''line 1 line2''' 'line 1\n\ line2' So I would try to rewrite the statement with: sql = """INSERT INTO table1 (field1, field2, field3) VALUES ('abc', 'abc', TRUE), ('def', 'def', FALSE), ('ghi', 'ghi', TRUE);""" pypl.execute(sql) > > Thanks for any suggestions and help in advance, > Andi >