[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: RE: no_data_found oracle vs pg
From: "Jean-Marc Voillequin (MA)" <Jean-Marc.Voillequin () moodys ! com>
Date: 2023-09-18 14:05:24
Message-ID: MN2PR20MB2735F1267E36D5C5A4E57BA8BEFBA () MN2PR20MB2735 ! namprd20 ! prod ! outlook ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Thanks David,
It's a very interesting idea.
From: David Rowley <dgrowleyml@gmail.com>
Sent: Monday, September 18, 2023 3:14 PM
To: Jean-Marc Voillequin (MA) <Jean-Marc.Voillequin@moodys.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql@postgresql.org
Subject: Re: no_data_found oracle vs pg
On Mon, 18 Sept 2023 at 18: 49, Jean-Marc Voillequin (MA) \
<Jean-Marc. Voillequin@ moodys. com> wrote: > I know I can test the ROWCOUNT or \
the FOUND indicator, but it's not what I want. > > I want a NO_DATA_FOUND exception \
to be raised ZjQcmQRYFpfptBannerStart
This email originated from outside of Moody's
Do not click links or open attachments unless you recognize the sender and know the \
content is safe. ZjQcmQRYFpfptBannerEnd
On Mon, 18 Sept 2023 at 18:49, Jean-Marc Voillequin (MA)
<Jean-Marc.Voillequin@moodys.com<mailto:Jean-Marc.Voillequin@moodys.com>> wrote:
> I know I can test the ROWCOUNT or the FOUND indicator, but it's not 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 when called from \
> SQL.
It would mean having to include logic in each function, but perhaps
GET DIAGNOSTIC PG_CONTEXT could be of some use.
You could adapt the following to call the STRICT or non-STRICT version
accordingly.
create or replace function myfunc() returns int as $$
declare ctx text;
begin
GET DIAGNOSTICS ctx = PG_CONTEXT;
if split_part(ctx, E'\n', 2) = '' then
raise notice 'top level';
else
raise notice 'nested';
end if;
return 1;
end;
$$ language plpgsql;
create or replace function callerfunc() returns int as $$
begin
return myfunc();
end;
$$ language plpgsql;
select myfunc();
select callerfunc();
David
----------------------------------------------------------------------
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 in 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 this 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 message 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 keep 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.
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:DengXian;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@DengXian";
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:10.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas",serif;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span lang="FR" style="font-size:11.0pt">Thanks \
David,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:11.0pt">It's \
a very interesting idea.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt"><o:p> </o:p></span></p> <div \
style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in"> <p \
class="MsoNormal"><b><span style="font-size:11.0pt">From:</span></b><span \
style="font-size:11.0pt"> David Rowley <dgrowleyml@gmail.com> <br>
<b>Sent:</b> Monday, September 18, 2023 3:14 PM<br>
<b>To:</b> Jean-Marc Voillequin (MA) <Jean-Marc.Voillequin@moodys.com><br>
<b>Cc:</b> Pavel Stehule <pavel.stehule@gmail.com>; \
pgsql-sql@postgresql.org<br> <b>Subject:</b> Re: no_data_found oracle vs \
pg<o:p></o:p></span></p> </div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal" style="mso-line-height-alt:.75pt"><span \
style="font-size:1.0pt;color:white">On Mon, 18 Sept 2023 at 18: 49, Jean-Marc \
Voillequin (MA) <Jean-Marc. Voillequin@ moodys. com> wrote: > I know I \
can test the ROWCOUNT or the FOUND indicator, but it's not what I want. > > I \
want a NO_DATA_FOUND exception to be raised <o:p></o:p></span></p> </div>
<div>
<p class="MsoNormal" style="mso-line-height-alt:.75pt"><span \
style="font-size:1.0pt;color:white">ZjQcmQRYFpfptBannerStart<o:p></o:p></span></p> \
</div> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" \
width="100%" style="width:100.0%;border-radius:4px"> <tbody>
<tr>
<td style="padding:12.0pt 0in 12.0pt 0in">
<table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="100%" \
style="width:100.0%;background:#FFEB9C;border:none;border-top:solid #E7C900 3.0pt"> \
<tbody> <tr>
<td valign="top" style="border:none;padding:0in 7.5pt 3.75pt 4.5pt">
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" \
align="left"> <tbody>
<tr>
<td style="padding:3.0pt 6.0pt 3.0pt 6.0pt">
<p class="MsoNormal"><b><span \
style="font-size:10.5pt;font-family:"Arial",sans-serif;color:black">This \
email originated from outside of Moody's <o:p></o:p></span></b></p>
</td>
</tr>
<tr>
<td style="padding:3.0pt 6.0pt 3.0pt 6.0pt">
<p class="MsoNormal"><span \
style="font-size:9.0pt;font-family:"Arial",sans-serif;color:black">Do not \
click links or open attachments unless you recognize the sender and know the content \
is safe. <o:p></o:p></span></p>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<div>
<p class="MsoNormal" style="mso-line-height-alt:.75pt"><span \
style="font-size:1.0pt;color:white">ZjQcmQRYFpfptBannerEnd<o:p></o:p></span></p> \
</div> <pre style="white-space:pre-wrap"><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">On Mon, 18 Sept \
2023 at 18:49, Jean-Marc Voillequin (MA)<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><<a \
href="mailto:Jean-Marc.Voillequin@moodys.com">Jean-Marc.Voillequin@moodys.com</a>> \
wrote:<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">> I know I can \
test the ROWCOUNT or the FOUND indicator, but it's not what I \
want.<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">> 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 when called from \
SQL.<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">It \
would mean having to include logic in each function, but \
perhaps<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">GET DIAGNOSTIC \
PG_CONTEXT could be of some use.<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">You \
could adapt the following to call the STRICT or non-STRICT \
version<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">accordingly.<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">create \
or replace function myfunc() returns int as $$<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">declare ctx \
text;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">begin<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">GET \
DIAGNOSTICS ctx = PG_CONTEXT;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">if split_part(ctx, \
E'\n', 2) = '' then<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">raise notice 'top \
level';<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">else<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">raise \
notice 'nested';<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">end \
if;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">return \
1;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">end;<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">$$ \
language plpgsql;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">create \
or replace function callerfunc() returns int as $$<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">begin<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">return \
myfunc();<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">end;<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">$$ \
language plpgsql;<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">select \
myfunc();<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif">select \
callerfunc();<o:p></o:p></span></pre> <pre><span \
style="font-size:11.0pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Arial",sans-serif">David<o:p></o:p></span></pre>
</div>
<HR>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 in 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 this 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 message 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 keep 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.<BR> </body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic