[FIXED] How do I select the comment on a Postgres stored procedure

Issue

Suppose I have a procedure, call it my_procedure:

CREATE OR REPLACE my_procedure(X INT);

Then I write:

COMMENT ON my_procedure IS 'My comment'

How do I then use a SELECT statement to retrieve that comment?

This looks promising:

SELECT *
FROM pg_description
WHERE objoid = WHAT_SHOULD_THIS_BE?;

but I have no idea how to find the objoid of my_procedure.

Any help is appreciated, thanks!

Solution

  SELECT pg_catalog.obj_description(p.oid, 'pg_proc') as "comment"
  FROM pg_catalog.pg_proc AS p
  WHERE p.proname = 'my_procedure';

You may want to also check the values in the pronamespace and proargtypes and prokind columns if there's a chance of multiple procedures or functions having the same name

I got this information by using psql -E and looking at the queries it made.

Answered By - Jasen

Answer Checked By - Candace Johnson (PHPFixing Volunteer)