Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qi8AB-005GlB-SV for pgsql-sql@arkaria.postgresql.org; Mon, 18 Sep 2023 06:55:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qi8AA-00F930-2D for pgsql-sql@arkaria.postgresql.org; Mon, 18 Sep 2023 06:55:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qi8A9-00F92l-GR for pgsql-sql@lists.postgresql.org; Mon, 18 Sep 2023 06:55:17 +0000 Received: from mail-qk1-x731.google.com ([2607:f8b0:4864:20::731]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qi8A6-005Cg2-Bu for pgsql-sql@postgresql.org; Mon, 18 Sep 2023 06:55:16 +0000 Received: by mail-qk1-x731.google.com with SMTP id af79cd13be357-76f18e09716so271097685a.2 for ; Sun, 17 Sep 2023 23:55:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1695020113; x=1695624913; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=tZZkZwqpb8tc0+4ANY969ZoQgto8CUoW8d6/HOdqCeE=; b=dt5nibb7fneTlrp5MFQ01Bysq4g4YVV+v8gdza1CjzEJadp5HlE7j8RcSfP9e5wkP0 +UE8mI79yFmzxrF2zOvXAgT6vmAA0SzFEEGD+fVe+a9mXoSeP3fKvqxsFBneiMt+8ezP oOcowTzBQUA6SwOoCVqoj3mk9Ld9fo731esRgbDNTtXxMRsislj7r1sbHY28R+ih6CJ1 sG4Mta6H1LiYYp89pbrtCmvqGVSCGtfMB7LjXUpuKQ5rVJLoK7hXCGRiKD591/4/gkdC ok17dnVp0NkfFET267GqCpKGHwes/V1vUljFNXyM+08p5iAxMEQoHWjIyQ/Q6Jj6bGyv YlGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1695020113; x=1695624913; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=tZZkZwqpb8tc0+4ANY969ZoQgto8CUoW8d6/HOdqCeE=; b=wO+Id1iIEsrtmRZOYONcWOAv90IZjakfF/8VIJrQEMKfc6nDCAM3t5CFBcMwUPkZyJ tVCIcmhrgdAB4ASg4G8AiuB6o5jnFuh0e7w6poPuujXlA7fPKIq1I+oqxMpTjt4CkPcJ uRTgRn3rXlip8u0rusYVik8sY7B01QEty48Y1bN0L9+JetUiIeQKRCzC18CygH+9mnGg p7O2NMIaRLOCfjLWazvtDmfylBuULjRucgL1vEv0kcTFaBybXv/qqlzILGVehY7fAqbV EdaigHLLDlt1C5Auhb+ztYuGQt3zh8kvsfTYUydXF3VZGKIZNI8BYQbfaiBcyUaYHkou dL2g== X-Gm-Message-State: AOJu0Yz2BEaAhRv2IXVdp3P1YwpfniVxTZAdJ4P1nVZA+mVQrA9O2T1Z FBTgbzAbtlzbCUYdzjmSBAxLLQ4fINcnSi5w7N8= X-Google-Smtp-Source: AGHT+IGCWWqx6OAp/Z9Tl0y1fm0GPyHJLwXoQvjK5eWE/wAymWJe4EGXLdRdV9f9mxMzJxhb8er2Za/abFj65So6oSI= X-Received: by 2002:a05:620a:944:b0:76f:11d5:6532 with SMTP id w4-20020a05620a094400b0076f11d56532mr8437578qkw.76.1695020113451; Sun, 17 Sep 2023 23:55:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Mon, 18 Sep 2023 08:54:42 +0200 Message-ID: Subject: Re: no_data_found oracle vs pg To: "Jean-Marc Voillequin (MA)" Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary="000000000000bfe11c06059c9fc0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bfe11c06059c9fc0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi po 18. 9. 2023 v 8:49 odes=C3=ADlatel Jean-Marc Voillequin (MA) < Jean-Marc.Voillequin@moodys.com> napsal: > Thanks Pavel, > > > > I know I can test the ROWCOUNT or the FOUND indicator, but it=E2=80=99s n= ot what I > want. > > I want a NO_DATA_FOUND exception to be raised when the function is called > from a PL/pgSQL block, and I want the function to return a NULL value whe= n > called from SQL. > you cannot change it. There is not any possibility. Regards Pavel > > Regards > > > > *From:* Pavel Stehule > *Sent:* Saturday, September 16, 2023 4:06 PM > *To:* Jean-Marc Voillequin (MA) > *Cc:* pgsql-sql@postgresql.org > *Subject:* Re: no_data_found oracle vs pg > > > > Hi so 16. 9. 2023 v 13: 27 odes=C3=ADlatel Jean-Marc Voillequin (MA) > napsal: Hello everyone, On Oracle, a > no_data_found exception is raised from pl/sql but not from sql (it return= s > null). It's well known. > > ZjQcmQRYFpfptBannerStart > > *This email originated from outside of Moody's * > > Do not click links or open attachments unless you recognize the sender an= d > know the content is safe. > > ZjQcmQRYFpfptBannerEnd > > Hi > > > > so 16. 9. 2023 v 13:27 odes=C3=ADlatel Jean-Marc Voillequin (MA) < > Jean-Marc.Voillequin@moodys.com> napsal: > > 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(),'') from dual; > > COALESCE(HELLO(),'') > > -------------------------------------------------------------------------= ------- > > > 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 > plpgsql 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(),''); > > 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 languag= e > 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(),''); > coalesce > ---------- > > (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 > helpful: > select do_not_raise_no_data_found(hello_strict()); > > > > The STRICT keyword can be replaced by test of number of returned rows > > > > so you can do some like > > > > DECLARE rows int; target record; > > BEGIN > > SELECT * FROM foo INTO target; > > GET DIAGNOSTICS rows =3D ROW_COUNT; > > IF rows <> 1 THEN > > /* do what you want */ > > END IF; > > END; > > > > Regards > > > > Pavel > > > > > > > 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 > join operator, connect by, packages, etc). > 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 > compliance purposes and to protect its customers, employees and business > and where allowed to do so by applicable law. The information contained i= n > this e-mail message, and any attachment thereto, is confidential and may > not be disclosed without our express permission. If you are not the > intended recipient or an employee or agent responsible for delivering thi= s > message to the intended recipient, you are hereby notified that you have > received this message in error and that any review, dissemination, > distribution or copying of this message, or any attachment thereto, in > whole or in part, is strictly prohibited. If you have received this messa= ge > in error, please immediately notify us by telephone, fax or e-mail and > delete the message and all of its attachments. Every effort is made to ke= ep > our network free from viruses. You should, however, review this e-mail > message, as well as any attachment thereto, for viruses. We take no > responsibility and have no liability for any computer virus which may be > transferred via this e-mail message. > > ------------------------------ > Moody's monitors email communications through its networks for regulatory > compliance purposes and to protect its customers, employees and business > and where allowed to do so by applicable law. The information contained i= n > this e-mail message, and any attachment thereto, is confidential and may > not be disclosed without our express permission. If you are not the > intended recipient or an employee or agent responsible for delivering thi= s > message to the intended recipient, you are hereby notified that you have > received this message in error and that any review, dissemination, > distribution or copying of this message, or any attachment thereto, in > whole or in part, is strictly prohibited. If you have received this messa= ge > in error, please immediately notify us by telephone, fax or e-mail and > delete the message and all of its attachments. Every effort is made to ke= ep > our network free from viruses. You should, however, review this e-mail > message, as well as any attachment thereto, for viruses. We take no > responsibility and have no liability for any computer virus which may be > transferred via this e-mail message. > --000000000000bfe11c06059c9fc0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

po 18. 9. 2023 v=C2=A08:49 odes=C3=ADlatel Jean-Mar= c Voillequin (MA) <Je= an-Marc.Voillequin@moodys.com> napsal:

Thanks Pavel,=

=C2=A0

I know I can test the ROWCOUNT or the FOUND indicato= r, but it=E2=80=99s not what I want.

I want a NO_DATA_FOUND exception to be raised when t= he function is called from a PL/pgSQL block, and I want the function to ret= urn a NULL value when called from SQL.


you cannot change it.=C2=A0 There is not any possibility.=

Regards

Pavel
=

=C2=A0

Regards

=C2=A0

From: Pavel Stehule <pavel.stehule@gmail.com> Sent: Saturday, September 16, 2023 4:06 PM
To: Jean-Marc Voillequin (MA) <Jean-Marc.Voillequin@moodys.com><= br> Cc: pg= sql-sql@postgresql.org
Subject: Re: no_data_found oracle vs pg

=C2=A0

Hi so 16. = 9. 2023 v=C2=A013:=E2=80=8A27 odes=C3=ADlatel Jean-Marc Voillequin (MA) <= ;Jean-Marc.=E2=80=8AVoillequin@=E2=80=8Amoodys.=E2=80=8Acom> napsal: Hel= lo everyone, On Oracle, a no_data_found exception is raised from pl/sql but not from sql (it returns null). It's well kn= own.=E2=80=8A

ZjQcmQRYFp= fptBannerStart

This email originated from outside of Moody's

Do not click links or open attachments unless you recogn= ize the sender and know the content is safe.

ZjQcmQRYFp= fptBannerEnd

Hi

=C2=A0

so 16. 9. 2023 v=C2=A013:27 odes=C3=ADlatel Jean-Mar= c Voillequin (MA) <Jean-Marc.Voillequin@moodys.com> napsal:

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 Productio= n

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

Function created.

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

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

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

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 p= lpgsql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>=C2=A0 =C2=A0select 'a' into strict c where 1=3D2;
JM$>=C2=A0 =C2=A0return c;
JM$> end;$function$;
CREATE FUNCTION

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

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

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

ERROR:=C2=A0 query returned no rows
CONTEXT:=C2=A0 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 langua= ge plpgsql as $function$
JM$> declare
JM$> c char;
JM$> begin
JM$>=C2=A0 =C2=A0select 'a' into c where 1=3D2;
JM$>=C2=A0 =C2=A0return c;
JM$> end;$function$;
CREATE FUNCTION

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

JM=3D> do $$declare
JM$> res char;
JM$> begin
JM$>=C2=A0 =C2=A0res:=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());

=C2=A0

The STRICT keyword can be replaced by test of number= of returned rows

=C2=A0

so you can do some like

=C2=A0

DECLARE rows int; target record;

BEGIN

=C2=A0 SELECT * FROM foo INTO target;<= /p>

=C2=A0 GET DIAGNOSTICS rows =3D ROW_COUNT;=

=C2=A0 IF rows <> 1 THEN

=C2=A0=C2=A0=C2=A0 /* do what you want */<= /u>

=C2=A0 END IF;

END;

=C2=A0

Regards

=C2=A0

Pavel

=C2=A0

=C2=A0


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 ((+) lef= t join operator, connect by, packages, etc).
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 regulato= ry compliance purposes and to protect its customers, employees and business= and where allowed to do so by applicable law. The information contained in= this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express perm= ission. If you are not the intended recipient or an employee or agent respo= nsible for delivering this message to the intended recipient, you are hereb= y notified that you have received this message in error and that any review, dissemination, distribution or = copying of this message, or any attachment thereto, in whole or in part, is= strictly prohibited. If you have received this message in error, please im= mediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Every eff= ort is made to keep our network free from viruses. You should, however, rev= iew this e-mail message, as well as any attachment thereto, for viruses. We= take no responsibility and have no liability for any computer virus which may be transferred via this e-ma= il message.

Moody's monitors email communications through its networks for regu= latory compliance purposes and to protect its customers, employees and busi= ness and where allowed to do so by applicable law. The information containe= d in this e-mail message, and any attachment thereto, is confidential and m= ay not be disclosed without our express permission. If you are not the inte= nded recipient or an employee or agent responsible for delivering this mess= age to the intended recipient, you are hereby notified that you have receiv= ed this message in error and that any review, dissemination, distribution o= r copying of this message, or any attachment thereto, in whole or in part, = is strictly prohibited. If you have received this message in error, please = immediately notify us by telephone, fax or e-mail and delete the message an= d all of its attachments. Every effort is made to keep our network free fro= m viruses. You should, however, review this e-mail message, as well as any = attachment thereto, for viruses. We take no responsibility and have no liab= ility for any computer virus which may be transferred via this e-mail messa= ge.
--000000000000bfe11c06059c9fc0--