Skip to content

Commit 2b9baa2

Browse files
Americassushantdhiman
Americas
authored and
sushantdhiman
committed
fix(postgres): update upsert regex to match the last RETURNING * (#11538)
1 parent a34399f commit 2b9baa2

2 files changed

Lines changed: 56 additions & 2 deletions

File tree

‎lib/dialects/postgres/query-generator.js‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -347,8 +347,12 @@ class PostgresQueryGenerator extends AbstractQueryGenerator {
347347
const insert = this.insertQuery(tableName, insertValues, model.rawAttributes, upsertOptions);
348348
const update = this.updateQuery(tableName, updateValues, where, upsertOptions, model.rawAttributes);
349349

350-
insert.query = insert.query.replace('RETURNING *', `RETURNING ${primaryField} INTO primary_key`);
351-
update.query = update.query.replace('RETURNING *', `RETURNING ${primaryField} INTO primary_key`);
350+
if (options.returning) {
351+
const returningRegex = /RETURNING \*(?![\s\S]*RETURNING \*)/;
352+
353+
insert.query = insert.query.replace(returningRegex, `RETURNING ${primaryField} INTO primary_key`);
354+
update.query = update.query.replace(returningRegex, `RETURNING ${primaryField} INTO primary_key`);
355+
}
352356

353357
return this.exceptionFn(
354358
'sequelize_upsert',

‎test/unit/dialects/postgres/query-generator.test.js‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,12 +44,14 @@ if (dialect.startsWith('postgres')) {
4444
expectation: 'CREATE DATABASE "myDatabase" ENCODING = \'UTF8\' LC_COLLATE = \'en_US.UTF-8\' LC_CTYPE = \'zh_TW.UTF-8\' TEMPLATE = \'template0\';'
4545
}
4646
],
47+
4748
dropDatabaseQuery: [
4849
{
4950
arguments: ['myDatabase'],
5051
expectation: 'DROP DATABASE IF EXISTS "myDatabase";'
5152
}
5253
],
54+
5355
arithmeticQuery: [
5456
{
5557
title: 'Should use the plus operator',
@@ -87,6 +89,7 @@ if (dialect.startsWith('postgres')) {
8789
expectation: 'UPDATE "myTable" SET "foo"="foo"- \'bar\''
8890
}
8991
],
92+
9093
attributesToSQL: [
9194
{
9295
arguments: [{ id: 'INTEGER' }],
@@ -1091,6 +1094,53 @@ if (dialect.startsWith('postgres')) {
10911094
}
10921095
],
10931096

1097+
upsertQuery: [
1098+
{
1099+
arguments: [
1100+
'myTable',
1101+
{ name: 'foo' },
1102+
{ name: 'foo' },
1103+
{ id: 2 },
1104+
{ primaryKeyField: 'id' },
1105+
{ returning: false }
1106+
],
1107+
expectation: 'CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert(OUT created boolean, OUT primary_key text) AS $func$ BEGIN INSERT INTO "myTable" ("name") VALUES (\'foo\'); created := true; EXCEPTION WHEN unique_violation THEN UPDATE "myTable" SET "name"=\'foo\' WHERE "id" = 2; created := false; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert();'
1108+
},
1109+
{
1110+
arguments: [
1111+
'myTable',
1112+
{ name: 'RETURNING *', json: '{"foo":"RETURNING *"}' },
1113+
{ name: 'RETURNING *', json: '{"foo":"RETURNING *"}' },
1114+
{ id: 2 },
1115+
{ primaryKeyField: 'id' },
1116+
{ returning: false }
1117+
],
1118+
expectation: 'CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert(OUT created boolean, OUT primary_key text) AS $func$ BEGIN INSERT INTO "myTable" ("name","json") VALUES (\'RETURNING *\',\'{"foo":"RETURNING *"}\'); created := true; EXCEPTION WHEN unique_violation THEN UPDATE "myTable" SET "name"=\'RETURNING *\',"json"=\'{"foo":"RETURNING *"}\' WHERE "id" = 2; created := false; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert();'
1119+
},
1120+
{
1121+
arguments: [
1122+
'myTable',
1123+
{ name: 'foo' },
1124+
{ name: 'foo' },
1125+
{ id: 2 },
1126+
{ primaryKeyField: 'id' },
1127+
{ returning: true }
1128+
],
1129+
expectation: 'CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert(OUT created boolean, OUT primary_key text) AS $func$ BEGIN INSERT INTO "myTable" ("name") VALUES (\'foo\') RETURNING "id" INTO primary_key; created := true; EXCEPTION WHEN unique_violation THEN UPDATE "myTable" SET "name"=\'foo\' WHERE "id" = 2 RETURNING "id" INTO primary_key; created := false; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert();'
1130+
},
1131+
{
1132+
arguments: [
1133+
'myTable',
1134+
{ name: 'RETURNING *', json: '{"foo":"RETURNING *"}' },
1135+
{ name: 'RETURNING *', json: '{"foo":"RETURNING *"}' },
1136+
{ id: 2 },
1137+
{ primaryKeyField: 'id' },
1138+
{ returning: true }
1139+
],
1140+
expectation: 'CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert(OUT created boolean, OUT primary_key text) AS $func$ BEGIN INSERT INTO "myTable" ("name","json") VALUES (\'RETURNING *\',\'{"foo":"RETURNING *"}\') RETURNING "id" INTO primary_key; created := true; EXCEPTION WHEN unique_violation THEN UPDATE "myTable" SET "name"=\'RETURNING *\',"json"=\'{"foo":"RETURNING *"}\' WHERE "id" = 2 RETURNING "id" INTO primary_key; created := false; END; $func$ LANGUAGE plpgsql; SELECT * FROM pg_temp.sequelize_upsert();'
1141+
}
1142+
],
1143+
10941144
removeIndexQuery: [
10951145
{
10961146
arguments: ['User', 'user_foo_bar'],

0 commit comments

Comments
 (0)