INFORMATION_SCHEMA.routines column routine_definition does not show the source
Hello
PostgreSQL 14 added the feature: "Allow SQL-language functions<https://www.postgresql.org/docs/14/sql-createfunction.html> and procedures<https://www.postgresql.org/docs/14/sql-createprocedure.html> to use SQL-standard function bodies."
Unfortunately the bodies of such routines are not visible in the column routine_definition of the view INFORMATION_SCHEMA.routines. In case of these routines the field contains the empty string instead of the routine body.
Please observe the difference:
START TRANSACTION;
CREATE OR REPLACE FUNCTION f_fahrenheit_celsius_old(numeric) RETURNS numeric AS $$
SELECT round((($1 - 32.0) * 5.0 / 9.0),3); $$ LANGUAGE sql
IMMUTABLE STRICT LEAKPROOF;
CREATE OR REPLACE FUNCTION f_fahrenheit_celsius_new(numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE STRICT LEAKPROOF
RETURN round((($1 - 32.0) * 5.0 / 9.0),3);
SELECT
routine_schema,
routine_name,
routine_definition
FROM INFORMATION_SCHEMA.routines
WHERE routine_name IN ('f_fahrenheit_celsius_old','f_fahrenheit_celsius_new');
ROLLBACK;
Best regards
Erki Eessaar
On Wed, Nov 3, 2021 at 7:49 AM Erki Eessaar <erki.eessaar@taltech.ee> wrote:
Unfortunately the bodies of such routines are not visible in the column
routine_definition of the view INFORMATION_SCHEMA.routines. In case of
these routines the field contains the empty string instead of the routine
body.
Thanks for the report!
The information schema query consults pg_proc.prosrc directly instead of
calling pg_get_functiondef(...) (which didn't exist when the original query
was written, and hasn't been wrong until now).
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
The information schema query consults pg_proc.prosrc directly instead of
calling pg_get_functiondef(...) (which didn't exist when the original query
was written, and hasn't been wrong until now).
pg_get_functiondef would produce more than we want, but it looks like
pg_get_function_sqlbody() would do.
BTW, while researching this I noted the header comment for
pg_get_functiondef:
* 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 the
* function body. To wit: the function body starts on a line that begins
* with "AS ", and no preceding line will look like that.
Needless to say, the SQL-function-body patch has ignored this advice
totally. At the very least this comment needs to be adjusted, but
I wonder if it's not telling us that \ef and/or \sf are broken.
regards, tom lane
Decided to ping this as I don't recall or see it getting patched and the
recent discussion regarding the prosrc field.
On Wed, Nov 3, 2021 at 9:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"David G. Johnston" <david.g.johnston@gmail.com> writes:
The information schema query consults pg_proc.prosrc directly instead of
calling pg_get_functiondef(...) (which didn't exist when the originalquery
was written, and hasn't been wrong until now).
pg_get_functiondef would produce more than we want, but it looks like
pg_get_function_sqlbody() would do.BTW, while researching this I noted the header comment for
pg_get_functiondef:* 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 the
* function body. To wit: the function body starts on a line that begins
* with "AS ", and no preceding line will look like that.Needless to say, the SQL-function-body patch has ignored this advice
totally. At the very least this comment needs to be adjusted, but
I wonder if it's not telling us that \ef and/or \sf are broken.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
Decided to ping this as I don't recall or see it getting patched and the
recent discussion regarding the prosrc field.
* 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 the
* function body. To wit: the function body starts on a line that begins
* with "AS ", and no preceding line will look like that.Needless to say, the SQL-function-body patch has ignored this advice
totally. At the very least this comment needs to be adjusted, but
I wonder if it's not telling us that \ef and/or \sf are broken.
Indeed, if you experiment with "\sf+" or "\ef" with a line number,
those features fail miserably for new-style SQL functions.
We could partially fix that by teaching psql to also recognize
"BEGIN" as the start of the function body. I say "partially"
because the whole point of that line-numbers feature is so that
you can figure out which line an error report is complaining about.
However, SQL functions don't report in terms of line numbers,
and never have, so maybe that's moot. It's fortunate that they
don't, because the reconstructed function text is likely to have
different line breaks than the original.
regards, tom lane
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
Attachments:
fix-psql-sf-ef-for-new-style-functions.patchtext/x-diff; charset=us-ascii; name=fix-psql-sf-ef-for-new-style-functions.patchDownload+24-26
Ping again as a Reddit poster just pointed this out.
On Thursday, December 1, 2022, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.)
Not sure I bring much to patch review here; but is the plan to commit this
first then update information_schema or do both in one go?
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
Ping again as a Reddit poster just pointed this out.
Huh? The patch you're replying to was committed two years ago.
regards, tom lane
On Tuesday, September 17, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
Ping again as a Reddit poster just pointed this out.
Huh? The patch you're replying to was committed two years ago.
The patch I’m replying to doesn’t fix the bug reported on this thread
though…which still exists.
CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE
null END
AS character_data) AS routine_definition,
David J.