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 1p292L-0000M2-3n for pgsql-general@arkaria.postgresql.org; Mon, 05 Dec 2022 10:49:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p292J-0007yZ-LC for pgsql-general@arkaria.postgresql.org; Mon, 05 Dec 2022 10:49:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p292I-0007yQ-S9 for pgsql-general@lists.postgresql.org; Mon, 05 Dec 2022 10:49:23 +0000 Received: from eastern.birch.relay.mailchannels.net ([23.83.209.55]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p292F-0000PL-UE for pgsql-general@lists.postgresql.org; Mon, 05 Dec 2022 10:49:21 +0000 X-Sender-Id: hostpapa|x-authuser|martijn@upscene.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 381E83C0701 for ; Mon, 5 Dec 2022 10:49:15 +0000 (UTC) Received: from s156.servername.online (unknown [127.0.0.6]) (Authenticated sender: hostpapa) by relay.mailchannels.net (Postfix) with ESMTPA id 72D513C1D67 for ; Mon, 5 Dec 2022 10:49:14 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1670237354; a=rsa-sha256; cv=none; b=4qXXa5FAqhLUKaPVF3wuVGMhSfY3x9W71P+Rfk3vSbv4lLnX1Y0FceZgpjq1DmssSBxaU3 HhVdxEhnty4BkXgCDbKhTA9EyhYhDMQxNKFVcLgcMPjijqcRigze3ARG5o4D9hE/DJrA/c sF5k6rh4eNb558nHckPVGUWiR6s2pHz+B7lOei+5qURwM7FLxErVfA74jmGPa2ncSCRLZt wBN9cpQ9nUXvLQXxMDKLuO2BB3ckiQbu4AHvvATRZ1aK0NOo5fmWBVxl7Cxcca9vJPzDME ZeQo/w4rOQCFQFxWYHq7EpOf6+q/WCbH9zZyp+5e2MFbetYPBC0tof/LvexWkw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1670237354; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references:dkim-signature; bh=FLI8deMiCar3G44qYwymYsc+oSo1dmvyF9FnAeWs5Oc=; b=tMnAP1tvYc4W4Q6lXy1YfGt199dUSBN7rYa/z2esePCaA4hAKjXLZINItw7IILfHI2/KY+ 9Z8ihUQSOGAIuYKuywMxWPuW26jkIRzQQjmRCjJz8xIAt0M+VH7y71K7QgXgSuWgygK/ww FM55z4Sciu/UzPE6Zu5vqB7WNN7IcHieBuzl+THLOP6P2vRiIDLigvOq1+j3NKJDFeeZ5o ZzzWGC7KOUpMStZoI5aKPrJJmHCHyaWJxj5P2eOdHsMYdEBw3lieZ7e9/FixsbImEWL3W/ TesxrDZTML6jhDw6Bph1CoNha27Sit/G0WE419cwV+i/fm2OA/6AuXA+NCkzEg== ARC-Authentication-Results: i=1; rspamd-84789cff4b-vlr9z; auth=pass smtp.auth=hostpapa smtp.mailfrom=m.tonies@upscene.com X-Sender-Id: hostpapa|x-authuser|martijn@upscene.com X-MC-Relay: Neutral X-MailChannels-SenderId: hostpapa|x-authuser|martijn@upscene.com X-MailChannels-Auth-Id: hostpapa X-Macabre-Vacuous: 51fff95d1c9726e0_1670237354878_2037936283 X-MC-Loop-Signature: 1670237354878:1478457513 X-MC-Ingress-Time: 1670237354878 Received: from s156.servername.online (s156.servername.online [204.44.192.71]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384) by 100.97.74.61 (trex/6.7.1); Mon, 05 Dec 2022 10:49:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=upscene.com ; s=default; h=Content-Type:MIME-Version:Date:Subject:In-Reply-To:References: To:From:Message-ID:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=KOYgK+tiWaQLzg1Ob0LQVHlqiqvV2qoCGNUoRPRcSfI=; b=cwVue+qgdjgxaaiRQZhsEAcbxp upaDJnxe9EbAKVas3xxvS3zrmCvHTxgYkXqdeTyySDahU9ZIX6UIkIZwQtA53V1OhFTFaCa/EIlRi 44y/y85QGs2zZw1yFCiKE2JgzpuRzRRQD0wwloSxFz30jnhhtels66ljnQHwUVJRBMicokJGT3rnI BIWuJZW5hcPVvA1j/r9mBM0QGIsNCOKacc2JDhUx46PbkCB6DQaMCIxd+5iac9cQPn2kHw/qgDvGX YWguDbB1vKdR56QpCKmcAUCWYtvjS+fA/4Nswl/uGhDAMNBHY/S+l8EZqEThFcI+5h9H+joPAO9Mm eG2SFO1g==; Received: from [45.144.219.154] (port=49281 helo=misc.upscene.om) by s156.servername.online with esmtpa (Exim 4.95) (envelope-from ) id 1p2927-00BllW-ET for pgsql-general@lists.postgresql.org; Mon, 05 Dec 2022 02:49:13 -0800 Received: from MARTIJNWS ( [192.168.1.70]) by upscene.com with SMTP (Mailtraq/2.17.7.3598) id UPSC716BE61B for pgsql-general@lists.postgresql.org; Mon, 05 Dec 2022 11:49:02 +0100 Message-ID: <2D11774D730A443FA99475517FDAFA7C@MARTIJNWS> From: "Martijn Tonies \(Upscene Productions\)" To: References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <1579912.1669908194@sss.pgh.pa.us> <661B1D571A89455FAE64C8F316D1EAA3@MARTIJNWS> In-Reply-To: Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour Date: Mon, 5 Dec 2022 11:48:53 +0100 MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_0309_01D9089F.8CC60430"; type="multipart/alternative" X-Priority: 3 X-MSMail-Priority: Normal Importance: Normal X-Mailer: Microsoft Windows Live Mail 16.4.3508.205 X-MimeOLE: Produced By Microsoft MimeOLE V16.4.3508.205 X-Hops: 1 X-AuthUser: martijn@upscene.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_0309_01D9089F.8CC60430 Content-Type: multipart/alternative; boundary="----=_NextPart_001_030A_01D9089F.8CC60430" ------=_NextPart_001_030A_01D9089F.8CC60430 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hello Jeremy, Yes and no=20 For Stored Functions, the =E2=80=9Csql_body=E2=80=9D can either be a = block with BEGIN ATOMIC ... END or a single statement RETURN expression; For a Stored Procedure, the =E2=80=9Csql_body=E2=80=9D is always the = block. Using =E2=80=9Clanguage SQL=E2=80=9D and an =E2=80=9Csql_body=E2=80=9D = (as per documentation) is certainly easier (no string constant) and more = compatible with the SQL standard. But if you do so, you loose the = possibility to retrieve your actual code from the database, which, IMO, = is very counter intuitive. Lots of people use, how shall we call it, =E2=80=98interactive = development=E2=80=99 in tools (like the one we create) and instead of = running a saved and modified script, you load the objects from the = database, you modify it in a database development tool, test, modify, = test etc. I understand that dependency tracking is useful, and automatic object = modification when doing object renaming is nice, but personally, that = would be a corner case compared to day-to-day development of stored = code. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, = MySQL, InterBase, NexusDB and Firebird. From: Jeremy Smith=20 Sent: Friday, December 2, 2022 3:10 PM To: pgsql-general@lists.postgresql.org=20 Subject: Re: Stored procedure code no longer stored in v14 and v15, = changed behaviour From a user point of view, can also be seen as a "regression", when an observable property of the system changes to a new different / incompatible way, to some extent. I'm not saying it is, still it is a change one discovers too late, creates pain to some, and is both worth reporting and discussing. Isn't this only true if you use the new sql_body/BEGIN ATOMIC syntax, = though? Can't you keep using the older AS 'definition' syntax and still = get the body of the function, unchanged, in the prosrc column? You = would, of course, lose the benefit of the dependency tracking. As a = user, though, I don't find it surprising that creating a function using = new syntax specifically designed to provide dependency tracking would = change the way the function is stored internally. To me, the new syntax gives the same benefits and drawbacks to SQL = functions that we already have with views, which are also re-written on = creation. -Jeremy ------=_NextPart_001_030A_01D9089F.8CC60430 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
Hello Jeremy,
 
Yes and no 3D"Winking
 
For Stored Functions, the =E2=80=9Csql_body=E2=80=9D can either be = a block with BEGIN=20 ATOMIC ... END or a single statement RETURN expression;
 
For a Stored Procedure, the =E2=80=9Csql_body=E2=80=9D is always = the block.
 
 
Using =E2=80=9Clanguage SQL=E2=80=9D and an = =E2=80=9Csql_body=E2=80=9D (as per documentation) is certainly=20 easier (no string constant) and more compatible with the SQL standard. = But if=20 you do so, you loose the possibility to retrieve your actual code from = the=20 database, which, IMO, is very counter intuitive.
 
Lots of people use, how shall we call it, =E2=80=98interactive = development=E2=80=99 in=20 tools (like the one we create) and instead of running a saved and = modified=20 script, you load the objects from the database, you modify it in a = database=20 development tool, test, modify, test etc.
 
I understand that dependency tracking is useful, and automatic = object=20 modification when doing object renaming is nice, but personally, that = would be a=20 corner case compared to day-to-day development of stored code.
 
With=20 regards,

Martijn Tonies
Upscene=20 Productions
https://www.upscene.com

Database Workbench - = developer=20 tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and=20 Firebird.
 
From: Jeremy Smith
Sent: Friday, December 2, 2022 3:10 PM
To: pgsql-general@lists.postgresql= .org=20
Subject: Re: Stored procedure code no longer stored in v14 = and v15,=20 changed behaviour
 
 

From=20 a user point of view, can also be seen as a "regression",
when an=20 observable property of the system changes to a new
different / = incompatible=20 way, to some extent. I'm not saying it is,
still it is a change one = discovers too late, creates pain to some,
and is both worth = reporting and=20 discussing.
 
Isn't this only true if you use the new sql_body/BEGIN ATOMIC = syntax,=20 though?  Can't you keep using the older AS 'definition' syntax and = still=20 get the body of the function, unchanged, in the prosrc column?  You = would,=20 of course, lose the benefit of the dependency tracking.  As a user, = though,=20 I don't find it surprising that creating a function using new syntax=20 specifically designed to provide dependency tracking would change the = way the=20 function is stored internally.
 
To me, the new syntax gives the same benefits and drawbacks to SQL=20 functions that we already have with views, which are also re-written on=20 creation.
 
     =20 -Jeremy
------=_NextPart_001_030A_01D9089F.8CC60430-- ------=_NextPart_000_0309_01D9089F.8CC60430 Content-Type: image/png; name="wlEmoticon-winkingsmile[1].png" Content-Transfer-Encoding: base64 Content-ID: <1DA45AE0AADD4D5F8BBD289C96D05C0A@MARTIJNWS> iVBORw0KGgoAAAANSUhEUgAAABMAAAATCAYAAAByUDbMAAAAAXNSR0IArs4c6QAAAARnQU1BAACx jwv8YQUAAAAJcEhZcwAADsIAAA7CARUoSoAAAAQESURBVDhPdZR7TJtVGMZPKS0gsA2kzWJNY8jc H5oYzWKicVNn/EMTL4thbmzFgZkjOIMmuK2xMROHgiVmTow6sS7iTEZJNlxNWy5CoVAouCELrFAd l8oo0HIpl9IPaB/f87U1YrY3eZLm63d+3/M+5z2H3alWHJmq5Q6lLmBT2hZaFBNzTVnwWbImpk2Z tqn6TJ2nNlMVe/XuhYGH5CtdSv1ytyIYGn4JG5OFiPiLgfl3Eb6di/WhZ7DSkY3Jum3B8Yvb9ANG Jo8t3VyL17crgt1KpzC2Dwh9Q/oKCFYCSx8BgfcRnnoLvV8egvW4WlTfd8fw1/dbe9zn0hQxRLS4 Iw7amCkAwkZg4ydAOE/As8BKGcFOwlXzOIYul2F13ivq5iUdhs/K4KpKcw6c/o/DlW5leduZB1Dz KvtXbnMewb4gd58Q7BQsRSoIS7OI1+rshOhwoTYZ/eWp5VEQD7tLIWCjll75BYgY4ba8GQX++jrB yNniKZiOpIkQg8EAjUYj/r5ygAHdEtw4kyr8fjpZzZY6FaWhEQ3c1sJNzuJym14m2AlYCrfD77KD vi9q6g+L+AxOhrkLcji1SaUs0Ka0b/g+jOYk6gKwRnkJ56iXCsqM/gu8h9Gre0QncdXnSjFuIGcO BuFKAhwlcjub/y3LG1nURwMX9XU0q9XPqMVS0RUW3gHm8kn0zKsFPEXAyBGgk2AdDGGLBB3Fci+b bcoSsFyJuWEdrLqn4Kh6EUMmDYKTtIiywgKfs6MEeQ249SQwmA1c2wJ0EchOaic1M7QVyQXmt9zr DftPoLVsD64WP4ZbDW8g+HdJzBEHHQOmDwJjzwE3HwauKymnJNERbKQWcmaSwHZU5mX8iKyPHqKF J9Go24Xeb58XM8L8cWqLHHHQOD0begTovx/ouSfaXgzEXa3+KEFzvszGvPUZ2mXnE7SwCGPWV1BX kI1r53cDPsqEtza6Fxh+lEBqoJfac0jhu8Tg+zkKgpXB86kUTYcTtey2MUM9WZchYDKXXORhuvMF 1OWrULMvE4PVOzFj2YG1HhXW7OmYMUoxqGeopZEZqSJQA0PExNCalyg07WdqcXD5oV1u2UHtPEva jcifu+D6QQ3z21tQvVeGip0SVDzIUP00g7mAwUXANYLAzDBWKoX1oFQvgnjx0z9i2OoUWu+jbGi3 BiibPgXC3elYb09CqJEhZGFYJydhEnfEQb5KKRoOJDiN+/93e/DTzw9toC6dQk4mycSBFHeNb38s bJ4Rb2384yjInMM23xrx8nzOUm5UpOr76az5DTKELieIAxnffr5rngopWjSJAm+tNoelxJbevfo+ SFE5tcm6rhK5jU92e5FcnCO+/Y2HE3XNOewONy1j/wBSx0pf+lqmqAAAAABJRU5ErkJggg== ------=_NextPart_000_0309_01D9089F.8CC60430--