Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phFrB-0005FJ-IF for pgsql-hackers@arkaria.postgresql.org; Tue, 28 Mar 2023 20:23:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phFrA-0006bD-1c for pgsql-hackers@arkaria.postgresql.org; Tue, 28 Mar 2023 20:23:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phFr9-0006b4-Lo for pgsql-hackers@lists.postgresql.org; Tue, 28 Mar 2023 20:23:47 +0000 Received: from mail-lj1-x229.google.com ([2a00:1450:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phFr3-0002fB-3B for pgsql-hackers@lists.postgresql.org; Tue, 28 Mar 2023 20:23:47 +0000 Received: by mail-lj1-x229.google.com with SMTP id a21so6688544ljq.10 for ; Tue, 28 Mar 2023 13:23:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680035020; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=b38d2J+Ahy2OJOve/6zGo3cpQ0Aadym7vJagktGQ8WA=; b=hN9ZA/rVnwnurvCWVf36xkzrTOY2wgIXjl67wR58Kc7A43RW5GZQX4tTUL8khq0o2I tEDQwnMtvCj8oRgDS11UiUyqHVlYMFj7JSDohaUr2UJ3ckGC2drmXIEH29h75QnbOKH6 S8T8oCbRyhOpguC+E5I6d/t7+zaEkDNuL1HFIu2+KhlMX5HIaUh3gkgyHY9lpA1UkTnh Ky33YMmhn1u8L5ZPs7uKbxZj1qJfIkUiVnNtzseEbxUR6vUXrRdbAE0rZf4eXcKUxHUk sL7UWFQwDBYVej9STEPcAQ50ABacHH6Yj3xgQaZ0+7crVFVHmjPxb7Z/VVEVhRilZqJN dAjQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680035020; 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=b38d2J+Ahy2OJOve/6zGo3cpQ0Aadym7vJagktGQ8WA=; b=z9BpVQRHQHH62VCZSQEJitJwBcplKPfkvrnvuhF1Vl+ERvQg6ActHz0eGYx7wpddCo njCoVdkBjjbZbW08L3oVVR+xAjc5zZ80hotJOxTquKzvub5m4VTW0msQ+jucLtRonKgP y8jRu+F5/LhlsdSnukYfX412tG4gQtCxyW4v8AyQ0D0N5jzt7asG4omNalERDuHSKvvf aboiC9lLhJMJbAgIJkoB4CnIDp6ADHZkLa4ndE8hcXk4MV0NhiOuU+61npwN9KZkvCSk n2x7qj0JOfOG7xgHdzFWZ4RIkDAahu16DkrNV94RK2Vb5eafRcndcRuJ6IlMJdRIfMhq KNhQ== X-Gm-Message-State: AAQBX9ewtaLAytbnmOoZotGVTXidQ95+T/WeCK87vR2QIMRbhJ21swCD ZawPWrOxzHD/TckoyrhT1wyfG0RvKl1K/xCkU9s= X-Google-Smtp-Source: AKy350Y7LbC2hvBRAvelkGt6QFOnBLssKdzEdbDlginOsXIcyL1BuJx2QISKE3ukdcpbRRh5hL0h9Cu35nEg+UHrrXI= X-Received: by 2002:a2e:a0c7:0:b0:29e:e7b7:dfd1 with SMTP id f7-20020a2ea0c7000000b0029ee7b7dfd1mr5054068ljm.6.1680035019806; Tue, 28 Mar 2023 13:23:39 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Corey Huinker Date: Tue, 28 Mar 2023 16:23:26 -0400 Message-ID: Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions To: Isaac Morland Cc: pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000090df4705f7fba2db" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000090df4705f7fba2db Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Mar 28, 2023 at 3:25=E2=80=AFPM Isaac Morland wrote: > On Mon, 19 Dec 2022 at 17:57, Corey Huinker > wrote: > >> >> Attached is my work in progress to implement the changes to the CAST() >> function as proposed by Vik Fearing. >> >> CAST(expr AS typename NULL ON ERROR) >> will use error-safe functions to do the cast of expr, and will retur= n >> NULL if the cast fails. >> >> CAST(expr AS typename DEFAULT expr2 ON ERROR) >> will use error-safe functions to do the cast of expr, and will retur= n >> expr2 if the cast fails. >> > > Is there any difference between NULL and DEFAULT NULL? > What I think you're asking is: is there a difference between these two statements: SELECT CAST(my_string AS integer NULL ON ERROR) FROM my_table; SELECT CAST(my_string AS integer DEFAULT NULL ON ERROR) FROM my_table; And as I understand it, the answer would be no, there is no practical difference. The first case is just a convenient shorthand, whereas the second case tees you up for a potentially complex expression. Before you ask, I believe the ON ERROR syntax could be made optional. As I implemented it, both cases create a default expression which then typecast to integer, and in both cases that expression would be a const-null, so the optimizer steps would very quickly collapse those steps into a plain old constant. --00000000000090df4705f7fba2db Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Mar 28, 2023 at 3:25=E2=80=AFPM I= saac Morland <isaac.morland@g= mail.com> wrote:
On Mon= , 19 Dec 2022 at 17:57, Corey Huinker <corey.huinker@gmail.com> wrote:

Attached is my work in progress to implement t= he changes to the CAST() function as proposed by Vik Fearing.
CAST(expr AS typename NULL ON ERROR)
=C2=A0 =C2= =A0 will use error-safe functions to do the cast of expr, and will return N= ULL if the cast fails.

CAST(expr AS typename = DEFAULT expr2 ON ERROR)
=C2=A0 =C2=A0 will use error-safe functions to d= o the cast of expr, and will return expr2 if the cast fails.

Is there any difference between N= ULL and DEFAULT NULL?=C2=A0

What I t= hink you're asking is: is there a difference between these two statemen= ts:

SELECT= CAST(my_string AS integer NULL ON ERROR) FROM my_table;
=

<= /font>
SELECT CAST= (my_string AS integer DEFAULT NULL ON ERROR) FROM my_table;

And as I understand it,= the answer would be no, there is no practical difference. The first case i= s just a convenient shorthand, whereas the second case tees you up for a po= tentially complex expression. Before you ask, I believe the ON ERROR syntax= could be made optional.=C2=A0As I implemented it, both cases create a defa= ult expression which then typecast to integer, and in both cases that expre= ssion would be a const-null, so the optimizer steps would very quickly coll= apse those steps into a plain old constant.


=C2=A0
--00000000000090df4705f7fba2db--