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 1p0l2F-0006bD-4K for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 14:59:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0l2D-00017i-SR for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 14:59:33 +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 1p0l2D-00015W-AN for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 14:59:33 +0000 Received: from cross.elm.relay.mailchannels.net ([23.83.212.46]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0l27-00054z-SV for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 14:59:32 +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 2EB986C082D for ; Thu, 1 Dec 2022 14:59:22 +0000 (UTC) Received: from s156.servername.online (unknown [127.0.0.6]) (Authenticated sender: hostpapa) by relay.mailchannels.net (Postfix) with ESMTPA id 95F126C1DD0 for ; Thu, 1 Dec 2022 14:59:21 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1669906761; a=rsa-sha256; cv=none; b=93W6OcbsDjYMkrzwvLloIRZqfLhg7ZYkuF1nTutX1ECMQ1yri7HZ69+RanjnAhxLCrnTSx ZpZseeczDjds00UcDP5RSzWv7W3s/jyeJRYmc2cnX8ZkyBQU1tEM/DwrS+Egfm7qtw6m30 meUeKaDiZI+WRPrP9dQHUSSmx16gK570PNSSg581ejTQljCZoKAITYKYitBxECnVU6OO9t 5mGYjAuB7y5xUYibzwiB44aDtCKZgowttmuJXWTdQvS2xkQM9O0zVpLM6NFbtsiF8f9RuS ybPVZEdpaB6SNgwpF6WeuuG0F0BePrhc1vyrVcLQdcDYU9B+63bKtVZUk5cL0w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1669906761; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding:dkim-signature; bh=VewpTAL8YrXOMkoiUKIohgNH+p9PZbXiKK5Yxss70jw=; b=I1TNXJzwkPJrhcguWMJ4PabLc34S8NYCOnFlOnKyhJ7rTpxBTfW8zs+nwEx056rfim9mIV xYbcFhoy81/CRhiHtYUdbPKXgAzBmPFY+Kfzedb6tpjVFwov+85Tvcxddem57lcQIH+kxB eG+Jx/AjoZusLjz/qCwrxT4xaIqiroVvw0VgFyo66CabynAybsfK6crwYj7LdCr3XcPRnT WxmS5+NiMU2KgAAjLCIcTF9hxlJvmbIPd5cpl6Sf38Sba9Zr1ZwamdVOBQfyfa92tfGHCD Y5fK7xEB04fexEIF8hFh19pT/HDuwzB5zJmoeaEHsSHHsDeuUk5iWmM2M8qG/w== ARC-Authentication-Results: i=1; rspamd-7bd68c5946-v5j7s; 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-Cure-Oafish: 273209fd55361690_1669906761887_1800508042 X-MC-Loop-Signature: 1669906761887:760067881 X-MC-Ingress-Time: 1669906761887 Received: from s156.servername.online (s156.servername.online [204.44.192.71]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384) by 100.107.134.116 (trex/6.7.1); Thu, 01 Dec 2022 14:59:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=upscene.com ; s=default; h=Content-Transfer-Encoding:Content-Type:MIME-Version:Date: Subject:To:From:Message-ID:Sender:Reply-To:Cc:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=VewpTAL8YrXOMkoiUKIohgNH+p9PZbXiKK5Yxss70jw=; b=Kpn9esQSMJk6n3t6yenariHgHb nIlGt4oej8Xo5LGgkLrZsSypuxdcaOErDCGgwiBgnLpnwH9XER121zKsCnNuuAfA78xIBpNjVgt8L psDencAOgmNwwItLLMcIMrxmho6EUyP5KvBPjs+qfiq7NN+44+CkN+mCehkS4EJst/C+jz7rP1Vd3 FRTCkU9tSS9GsB+Z5noltE/+KF48b2OXa+e9bh7Iz2tOO00PdnbP2Sfu7HJqKYIccrzviDfjPA/Kc f+HAPsYYPUlwcbcEGEOJ60fBJhRa9rzLkDdNfTh1FvUHGXygBWQ/UBeVSdcdKUEOzAYJ0uVsuMHX4 xY8xRnag==; Received: from [45.144.219.154] (port=55619 helo=misc.upscene.om) by s156.servername.online with esmtpa (Exim 4.95) (envelope-from ) id 1p0l1z-009Gi6-DM for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 06:59:21 -0800 Received: from MARTIJNWS ( [192.168.1.70]) by upscene.com with SMTP (Mailtraq/2.17.7.3598) id UPSC710FE0A5 for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 15:59:03 +0100 Message-ID: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> From: "Martijn Tonies \(Upscene Productions\)" To: Subject: Stored procedure code no longer stored in v14 and v15, changed behaviour Date: Thu, 1 Dec 2022 15:58:37 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 8bit 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 Hi all, In PosgreSQL version 13, the source code for a stored procedure or function in SQL/plpgsql/etc was stored in pg_proc.prosrc. This column would hold the original procedure or function body, verbatim. Since version 14, the source code for a stored procedure or function written in plain (compound) SQL, a new feature, is no longer stored in pg_proc.prosrc, instead, there’s an additional column prosqlbody which returns some kind of pre-parsed SQL which has no use for the user. I know you can use pg_get_functiondef to get a CREATE PROCEDURE/FUNCTION statement, but for compound SQL based routines in v14 and v15, this removes all formatting and comments in the original CREATE statement. For database development, including browsing an existing database and know what each routine can do, this is quite terrible. What is the reason for this? Can you modify the server code to store the original body in proc.prosrc again? It would be very helpful. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.