CODEWITHSUNDEEP
sqlpostgresqlfunctionstored-procedurescomments
jasonmoth
jasonmoth

Reputation: 3

How do I select the comment on a Postgres stored procedure

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!

Upvotes: 0

Views: 206

Answers (1)

Jasen
Jasen

Reputation: 12412

  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.

Upvotes: 2

Related Questions