From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Erki Eessaar <erki.eessaar@taltech.ee>,
        Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: INFORMATION_SCHEMA.routines column routine_definition does not
 show the source
In-reply-to: <1744742.1669944519@sss.pgh.pa.us>
References: 
 <AM9PR01MB8268D5CDABDF044EE9F42173FE8C9@AM9PR01MB8268.eurprd01.prod.exchangelabs.com>
 <CAKFQuwZaihWCaR4T=4Ov9ufk7jipRbBBtEDon61XMYOU6qkhxQ@mail.gmail.com>
 <134216.1635957998@sss.pgh.pa.us>
 <CAKFQuwYT+HudtXnYTp_EvPvjd_a0516_PmmzJWX2SLQ+PLhqfg@mail.gmail.com>
 <1744742.1669944519@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 01 Dec 2022 20:28:39 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1852526.1669948861.0@sss.pgh.pa.us>
Date: Thu, 01 Dec 2022 21:41:30 -0500
Message-ID: <1852623.1669948890@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1852623.1669948890%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1852526.1669948861.1@sss.pgh.pa.us>

I wrote:
> We could partially fix that by teaching psql to also recognize
> "BEGIN" as the start of the function body.

Looks like we need "RETURN " too, so more or less as attached.

(I didn't bother with a test case yet.  \sf wouldn't be too
hard to exercise, but I wonder how we could test \ef portably.)

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="fix-psql-sf-ef-for-new-style-functions.patch";
	charset="us-ascii"
Content-ID: <1852526.1669948861.2@sss.pgh.pa.us>
Content-Description: fix-psql-sf-ef-for-new-style-functions.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/rul=
eutils.c
index a20a1b069b..641df1cabe 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2867,8 +2867,8 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
  *
  * Note: if you change the output format of this function, be careful not
  * to break psql's rules (in \ef and \sf) for identifying the start of th=
e
- * function body.  To wit: the function body starts on a line that begins
- * with "AS ", and no preceding line will look like that.
+ * function body.  To wit: the function body starts on a line that begins=
 with
+ * "AS ", "BEGIN ", or "RETURN ", and no preceding line will look like th=
at.
  */
 Datum
 pg_get_functiondef(PG_FUNCTION_ARGS)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 7672ed9e9d..de6a3a71f8 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -168,8 +168,7 @@ static bool get_create_object_cmd(EditableObjectType o=
bj_type, Oid oid,
 								  PQExpBuffer buf);
 static int	strip_lineno_from_objdesc(char *obj);
 static int	count_lines_in_buf(PQExpBuffer buf);
-static void print_with_linenumbers(FILE *output, char *lines,
-								   const char *header_keyword);
+static void print_with_linenumbers(FILE *output, char *lines, bool is_fun=
c);
 static void minimal_error_message(PGresult *res);
 =

 static void printSSLInfo(void);
@@ -1201,17 +1200,19 @@ exec_command_ef_ev(PsqlScanState scan_state, bool =
active_branch,
 				/*
 				 * lineno "1" should correspond to the first line of the
 				 * function body.  We expect that pg_get_functiondef() will
-				 * emit that on a line beginning with "AS ", and that there
-				 * can be no such line before the real start of the function
-				 * body.  Increment lineno by the number of lines before that
-				 * line, so that it becomes relative to the first line of the
-				 * function definition.
+				 * emit that on a line beginning with "AS ", "BEGIN ", or
+				 * "RETURN ", and that there can be no such line before the
+				 * real start of the function body.  Increment lineno by the
+				 * number of lines before that line, so that it becomes
+				 * relative to the first line of the function definition.
 				 */
 				const char *lines =3D query_buf->data;
 =

 				while (*lines !=3D '\0')
 				{
-					if (strncmp(lines, "AS ", 3) =3D=3D 0)
+					if (strncmp(lines, "AS ", 3) =3D=3D 0 ||
+						strncmp(lines, "BEGIN ", 6) =3D=3D 0 ||
+						strncmp(lines, "RETURN ", 7) =3D=3D 0)
 						break;
 					lineno++;
 					/* find start of next line */
@@ -2528,15 +2529,8 @@ exec_command_sf_sv(PsqlScanState scan_state, bool a=
ctive_branch,
 =

 			if (show_linenumbers)
 			{
-				/*
-				 * For functions, lineno "1" should correspond to the first
-				 * line of the function body.  We expect that
-				 * pg_get_functiondef() will emit that on a line beginning
-				 * with "AS ", and that there can be no such line before the
-				 * real start of the function body.
-				 */
-				print_with_linenumbers(output, buf->data,
-									   is_func ? "AS " : NULL);
+				/* add line numbers */
+				print_with_linenumbers(output, buf->data, is_func);
 			}
 			else
 			{
@@ -5611,24 +5605,28 @@ count_lines_in_buf(PQExpBuffer buf)
 /*
  * Write text at *lines to output with line numbers.
  *
- * If header_keyword isn't NULL, then line 1 should be the first line beg=
inning
- * with header_keyword; lines before that are unnumbered.
+ * For functions, lineno "1" should correspond to the first line of the
+ * function body; lines before that are unnumbered.  We expect that
+ * pg_get_functiondef() will emit that on a line beginning with "AS ",
+ * "BEGIN ", or "RETURN ", and that there can be no such line before
+ * the real start of the function body.
  *
  * Caution: this scribbles on *lines.
  */
 static void
-print_with_linenumbers(FILE *output, char *lines,
-					   const char *header_keyword)
+print_with_linenumbers(FILE *output, char *lines, bool is_func)
 {
-	bool		in_header =3D (header_keyword !=3D NULL);
-	size_t		header_sz =3D in_header ? strlen(header_keyword) : 0;
+	bool		in_header =3D is_func;
 	int			lineno =3D 0;
 =

 	while (*lines !=3D '\0')
 	{
 		char	   *eol;
 =

-		if (in_header && strncmp(lines, header_keyword, header_sz) =3D=3D 0)
+		if (in_header &&
+			(strncmp(lines, "AS ", 3) =3D=3D 0 ||
+			 strncmp(lines, "BEGIN ", 6) =3D=3D 0 ||
+			 strncmp(lines, "RETURN ", 7) =3D=3D 0))
 			in_header =3D false;
 =

 		/* increment lineno only for body's lines */

------- =_aaaaaaaaaa0--