Horton

INFORMATION_SCHEMA.routines column routine_definition does not show the source

Erki Eessaar <erki.eessaar@taltech.ee>

View thread
From: Erki Eessaar <erki.eessaar@taltech.ee>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2021-11-03T14:42:16Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add some sanity checks in executor for query ID reporting

  2. Fix psql's \sf and \ef for new-style SQL functions.

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