From e552ce339e9ee4fe2c8adf2298d7bba94ba817ac Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 21 Oct 2009 16:33:04 +0000 Subject: [PATCH] - RETURNING is supported by 8.2+ - add docs for PG delete..returning --- lib/sqlalchemy/dialects/postgresql/base.py | 11 ++++++++--- test/sql/test_returning.py | 16 ++++++++-------- 2 files changed, 16 insertions(+), 11 deletions(-) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 061c3b066e..3c905e7d54 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -32,14 +32,14 @@ that when an :func:`~sqlalchemy.sql.expression.insert()` construct is executed u "executemany" semantics, the sequence is not pre-executed and normal PG SERIAL behavior is used. -PostgreSQL 8.3 supports an ``INSERT...RETURNING`` syntax which SQLAlchemy supports +PostgreSQL 8.2 supports an ``INSERT...RETURNING`` syntax which SQLAlchemy supports as well. A future release of SQLA will use this feature by default in lieu of sequence pre-execution in order to retrieve new primary key values, when available. INSERT/UPDATE...RETURNING ------------------------- -The dialect supports PG 8.3's ``INSERT..RETURNING`` and ``UPDATE..RETURNING`` syntaxes, +The dialect supports PG 8.2's ``INSERT..RETURNING``, ``UPDATE..RETURNING`` and ``DELETE..RETURNING`` syntaxes, but must be explicitly enabled on a per-statement basis:: # INSERT..RETURNING @@ -52,6 +52,11 @@ but must be explicitly enabled on a per-statement basis:: where(table.c.name=='foo').values(name='bar') print result.fetchall() + # DELETE..RETURNING + result = table.delete().returning(table.c.col1, table.c.col2).\\ + where(table.c.name=='foo') + print result.fetchall() + Indexes ------- @@ -468,7 +473,7 @@ class PGDialect(default.DefaultDialect): def initialize(self, connection): super(PGDialect, self).initialize(connection) - self.implicit_returning = self.server_version_info > (8, 3) and \ + self.implicit_returning = self.server_version_info > (8, 2) and \ self.__dict__.get('implicit_returning', True) def visit_pool(self, pool): diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py index 8ba754c67f..a36ce3cd88 100644 --- a/test/sql/test_returning.py +++ b/test/sql/test_returning.py @@ -36,7 +36,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): table.drop() @testing.exclude('firebird', '<', (2, 0), '2.0+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_column_targeting(self): result = table.insert().returning(table.c.id, table.c.full).execute({'persons': 1, 'full': False}) @@ -53,7 +53,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): @testing.fails_on('firebird', "fb can't handle returning x AS y") @testing.exclude('firebird', '<', (2, 0), '2.0+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_labeling(self): result = table.insert().values(persons=6).\ returning(table.c.persons.label('lala')).execute() @@ -63,7 +63,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): @testing.fails_on('firebird', "fb/kintersbasdb can't handle the bind params") @testing.fails_on('oracle+zxjdbc', "JDBC driver bug") @testing.exclude('firebird', '<', (2, 0), '2.0+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_anon_expressions(self): result = table.insert().values(goofy="someOTHERgoofy").\ returning(func.lower(table.c.goofy, type_=GoofyType)).execute() @@ -76,7 +76,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): assert row[0] == 30 @testing.exclude('firebird', '<', (2, 1), '2.1+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_update_returning(self): table.insert().execute([{'persons': 5, 'full': False}, {'persons': 3, 'full': False}]) @@ -87,7 +87,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): eq_(result2.fetchall(), [(1,True),(2,False)]) @testing.exclude('firebird', '<', (2, 0), '2.0+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_insert_returning(self): result = table.insert().returning(table.c.id).execute({'persons': 1, 'full': False}) @@ -117,7 +117,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): @testing.exclude('firebird', '<', (2, 1), '2.1+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') @testing.fails_on_everything_except('postgresql', 'firebird') def test_literal_returning(self): if testing.against("postgresql"): @@ -130,7 +130,7 @@ class ReturningTest(TestBase, AssertsExecutionResults): eq_([dict(row) for row in result4], [{'persons': 10}]) @testing.exclude('firebird', '<', (2, 1), '2.1+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_delete_returning(self): table.insert().execute([{'persons': 5, 'full': False}, {'persons': 3, 'full': False}]) @@ -180,7 +180,7 @@ class KeyReturningTest(TestBase, AssertsExecutionResults): table.drop() @testing.exclude('firebird', '<', (2, 0), '2.0+ feature') - @testing.exclude('postgresql', '<', (8, 2), '8.3+ feature') + @testing.exclude('postgresql', '<', (8, 2), '8.2+ feature') def test_insert(self): result = table.insert().returning(table.c.foo_id).execute(data='somedata') row = result.first() -- 2.47.2