From: | Erki Eessaar <erki(dot)eessaar(at)taltech(dot)ee> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | INFORMATION_SCHEMA.routines column routine_definition does not show the source |
Date: | 2021-11-03 14:42:16 |
Message-ID: | AM9PR01MB8268D5CDABDF044EE9F42173FE8C9@AM9PR01MB8268.eurprd01.prod.exchangelabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-11-03 15:21:34 | Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source |
Previous Message | PG Bug reporting form | 2021-11-03 11:48:12 | BUG #17270: Partitions are not prunned for generic plans when `ANY` construct is used. |