Populate INFORMATION_SCHEMA.ROUTINES and PARAMETERS

[lukaseder]

Describe the solution you'd like

For increased PostgreSQL compatibility, it would be useful to have user defined functions be listed in INFORMATION_SCHEMA.ROUTINES and PARAMETERS:

create function f (i text) returns text as $$ select i $$ language sql;
select * from information_schema.routines;

This just produces an empty result.

Describe alternatives you've considered

I can obviously reverse engineer pg_catalog.pg_proc:

select * from pg_catalog.pg_proc where proname = 'f'

But I find that table much less user friendly than the INFORMATION_SCHEMA one.

Additional context

This is for jOOQ source code generation: jOOQ/jOOQ#13947

Jira issue: CRDB-28347

Epic CRDB-27601

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_,ORM,jOOQ)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[lukaseder]

The workaround is probably to copy paste the PostgreSQL definition of the views and inline those wherever ROUTINES and PARAMETERS are referenced:
https://github.com/postgres/postgres/blob/master/src/backend/catalog/information_schema.sql

[haquem1]

@lukaseder I'd like to give this a shot. Just getting started in OSS and looking to level up my SQL foundational skills for data platforms!