From: "Jean-Marc Voillequin (MA)" <Jean-Marc.Voillequin@moodys.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: no_data_found oracle vs pg
Thread-Topic: no_data_found oracle vs pg
Thread-Index: Adnoe0OoLXOaVG7cSxyH3NXNcBCAcQ==
Date: Sat, 16 Sep 2023 09:26:29 +0000
Message-ID: 
 <MN2PR20MB273509335B549DEB90AB5724BEF5A@MN2PR20MB2735.namprd20.prod.outlook.com>
Accept-Language: fr-FR, en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?qAf4pcZY+WphHaXXksTipeAcxQyqFWk++cjQnfdjgX+TO0q2q+lHo9TtVtMV?=
 =?us-ascii?Q?a6iMnly4NGMQmgOVOXq2YMQy2/Yw2cHBi1GtXu3QPnk88gmXO1ERLPHwCtD9?=
 =?us-ascii?Q?dKENDvid1ef50QlhW2ms/oPuxd131qULcpCiSt4a/CvdNjM8xqYfWh574pLB?=
 =?us-ascii?Q?2Q36GkFbjDR+nqIVs1Z48+JuBRAD5O7Ln/zcI5gGlkWGtCAMShdBrkGoWie+?=
 =?us-ascii?Q?JMUDB1tdtxniBXFFyOIjMmWrNAdBKeXSCyVOxOppJWjfjhO159aKgLmMnAyw?=
 =?us-ascii?Q?8KCXcbO3NFWI1KJRqgT72e487dfhn5VWJNiBAJfzaRkY8H9Z12Pe3rc0Nx2V?=
 =?us-ascii?Q?ei+lUzcJ8b8QF5YGpW2FP1BSx0TJlu1pHjtPeWdKhLoV50gTsMX7chOLb7Ax?=
 =?us-ascii?Q?Fepf5deK3dp6CAnI6KoHKVxw4vmzVpF7foJeWsnhQImdzxeQe6PWC4W608b1?=
 =?us-ascii?Q?6uN48HPS/q++WIIp2/0pPSUeo3eXwGp76BCuydZDuNa04Ga+IU57fXA3+kQ1?=
 =?us-ascii?Q?BDx0qm1oyPhhkZaelzaFZ9SuFv7IbX21SC2idlMlx4jk+rWlKNKFs8nKhTWe?=
 =?us-ascii?Q?cGwePLz+HdOokMD8PKSiGoNtQ/pyroYQ8O1g7LmHgJBQv3WOV9611D20wTgj?=
 =?us-ascii?Q?kllOaRKDBnVX0W32kzRXMLURL0pGK+1vYBy+Ie0BW0SjFDCTWgKgLq9C4uWi?=
 =?us-ascii?Q?n9rsrs1bbQELQWl8fCfOT7BdnRnPSZ8bwvpEdIlYztOvvs2kVPv5zmRKuTn3?=
 =?us-ascii?Q?F5q16K3rcDBUHjrcoBrzhNJ7tLhZkHNKhSTbv2QPgUNvnBbAUXz3OW9rGNrb?=
 =?us-ascii?Q?vdL+GDNIE6u5/FcMqMz/DB6yeKlxRuoOu8ADKciN4wldBtMco9Tx8iUmI9MB?=
 =?us-ascii?Q?1tUQdX989eeUOOcQnsvz4iziKn1uwb1frxBO9ZOnU5pkh2eCqpYDOHU5dp8X?=
 =?us-ascii?Q?+GBgT/b3qCdHz0ZExmt1lNHGdHvNwRMX6BSqhFGU7Ej1uhPI28RQJXFhwicR?=
 =?us-ascii?Q?rEIDGZPveU0vPYEZFlCrK283b6Pv/2lrwbs7b55LyKPl7hqXcv4jhbbFH1EA?=
 =?us-ascii?Q?zKRJyy/0iLFbqNBvQwe1Gbx5f4Hbts1kThnr9Ok6dVh5Kyt4JK/ZiSmkryjE?=
 =?us-ascii?Q?0AmZe7xu4Gyfn95tLivQSRGZb6Zq8GnPfsRM376dWhXSBVFtUMVRSsMhIES7?=
 =?us-ascii?Q?boty3QsZPcBmiCriV3rQKwDiBwT5xhjmDikMlhUFkSxMRIZeEA6OZx/Zcxdc?=
 =?us-ascii?Q?UrXQQPqBfpt+kcKfLUI3AlWCDQuFNB+Ok+3n+guVSuWDbmB91JnJiIVE5Va7?=
 =?us-ascii?Q?h4G4NSxvgw4iJ4rRCEJh3QfUbrnhdpkIKy1o3TDNQHnFyOugPLMvVTyw2CxL?=
 =?us-ascii?Q?bgZx5Mw6F/EA6uWQbVslbZJnrMvo8f8BczFwsC6thmziwFhhDxnbqZ8IHND/?=
 =?us-ascii?Q?qPmw4YrfZDFgIpg+T1aToXEbwFqGYQ8srO5ensI5WY8Rd+HHqXGnc3Ur4Gfz?=
 =?us-ascii?Q?I2toiu2/yaI+g45vkk308O80Wv1+gHGMgImGZiarfWRCvInVv64mCvtiOIj+?=
 =?us-ascii?Q?10bdh1SUPfEtXap0RzRWPhzAzDq70ngL467DgT2z?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: moodys.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: MN2PR20MB2735.namprd20.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 35ca67a1-f28b-4008-58ba-08dbb6970239
X-MS-Exchange-CrossTenant-originalarrivaltime: 16 Sep 2023 09:26:29.6120
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 1061a8b8-b1ee-4249-bb84-9a2cd2792fae
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 tsq6ITHOT24QVTdIt/TAoudecuQSWjVYFfdDgJ05t69rR5YaDhB63snIZ1cGD39/rr16FWZruBvhGwotBFGLzg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLAPR20MB3748
X-Proofpoint-GUID: RfE3mMYgXkxIvxopnXgYOZh_cKQM2DaM
X-Proofpoint-ORIG-GUID: RfE3mMYgXkxIvxopnXgYOZh_cKQM2DaM
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.267,Aquarius:18.0.980,Hydra:6.0.601,FMLib:17.11.176.26
 definitions=2023-09-15_20,2023-09-15_01,2023-05-22_02
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 suspectscore=0 bulkscore=0
 mlxlogscore=346 malwarescore=0 phishscore=0 spamscore=0 mlxscore=0
 clxscore=1011 lowpriorityscore=0 adultscore=0 impostorscore=0
 priorityscore=1501 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.12.0-2308100000 definitions=main-2309160081
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/MN2PR20MB273509335B549DEB90AB5724BEF5A%40MN2PR20MB2735.namprd20.prod.outlook.com>
Precedence: bulk

Hello everyone,

On Oracle, a no_data_found exception is raised from pl/sql but not from sql=
 (it returns null). It's well known.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create or replace function hello return char is
  2  c char;
  3  begin
  4     select 'a' into c from dual where 1=3D2;
  5     return c;
  6  end;
  7  /

Function created.

SQL> select coalesce(hello(),'<NULL>') from dual;

COALESCE(HELLO(),'<NULL>')
---------------------------------------------------------------------------=
-----
<NULL>

SQL> declare
  2  res char;
  3  begin
  4     res:=3Dhello();
  5  end;
  6  /

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "JM.HELLO", line 4
ORA-06512: at line 4


On PG, with the strict keyword, we get:

psql (15.2)
Type "help" for help.

JM=3D> create or replace function hello_strict() returns char language plpg=
sql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>   select 'a' into strict c where 1=3D2;
JM$>   return c;
JM$> end;$function$;
CREATE FUNCTION

JM=3D> select coalesce(hello_strict(),'<NULL>');

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function hello_strict() line 5 at SQL statement

JM=3D> do $$declare
JM$> res char;
JM$> begin
JM$>   res:=3Dhello_strict();
JM$> end$$;

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function hello_strict() line 5 at SQL statement
PL/pgSQL function inline_code_block line 4 at assignment


And without the strict keyword:

JM=3D> create or replace function hello_not_strict() returns char language =
plpgsql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>   select 'a' into c where 1=3D2;
JM$>   return c;
JM$> end;$function$;
CREATE FUNCTION

JM=3D> select coalesce(hello_not_strict(),'<NULL>');
 coalesce
----------
 <NULL>
(1 row)

JM=3D> do $$declare
JM$> res char;
JM$> begin
JM$>   res:=3Dhello_not_strict();
JM$> end$$;
DO
JM=3D>

I have tons of functions to migrate from Oracle to PG. They are both called=
 from SQL or PL/SQL.
I would like to avoid to create two functions (_strict and _not_strict).

A kind of proxy function that is lazy to evaluate its argument would be hel=
pful:
select do_not_raise_no_data_found(hello_strict());

Or maybe a parameter to set just prior to exec sql.
set do_not_raise_no_data_found_in_sql=3Dtrue;
select hello_strict();

Or something else.
Any good idea is welcome!

I've been able to transpose to PG all Oracle specific features ((+) left jo=
in operator, connect by, packages, etc).=20
It was a big challenge almost successful.
But I cannot figure out how to solve this strict/not strict difference in a=
 smart way. This is my last blocking point. It makes me crazy!

Thanks & Regards

----------------------------------------------------------------------
Moody's monitors email communications through its networks for regulatory c=
ompliance purposes and to protect its customers, employees and business and=
 where allowed to do so by applicable law. The information contained in thi=
s e-mail message, and any attachment thereto, is confidential and may not b=
e disclosed without our express permission. If you are not the intended rec=
ipient or an employee or agent responsible for delivering this message to t=
he intended recipient, you are hereby notified that you have received this =
message in error and that any review, dissemination, distribution or copyin=
g of this message, or any attachment thereto, in whole or in part, is stric=
tly prohibited. If you have received this message in error, please immediat=
ely notify us by telephone, fax or e-mail and delete the message and all of=
 its attachments. Every effort is made to keep our network free from viruse=
s. You should, however, review this e-mail message, as well as any attachme=
nt thereto, for viruses. We take no responsibility and have no liability fo=
r any computer virus which may be transferred via this e-mail message.