0

I'm trying to query a list of Functions and Procedures in EnterpriseDB 9.2. I want to distinguish between the two types of function, however, as PgAdmin does in its tree display. I'm trying to script the creation of grant statements for new users, but I need to be able to distinguish between them, because one requires 'grant execute on function', while the other requires 'grant execute on procedure'.

So far, I've tried querying the list from pg_catalog.pg_proc and from information_schema.routines. Both of these list everything as a function.

Any ideas on how I can get to something that will help me identify each by it's right type?

Henry
  • 600
  • 2
  • 7
  • 22
  • There is no difference between functions and procedures in PostgresQL, because there is no such thing as a "procedure" in PostgreSQL. You must not be using PostgreSQL. What are you actually using? `SELECT version()`. – Craig Ringer May 09 '14 at 12:35
  • 2
    (At a guess, you're actually using one of EDB's forks like Postgres Plus Advanced Server) – Craig Ringer May 09 '14 at 12:49
  • @CraigRinger, spot on. But then why do I have to `EXECUTE PROCEDURE` in a trigger, providing a function name? {;-) – Patrick May 09 '14 at 12:55
  • Because you aren't using PostgreSQL, you're using EnterpriseDB Postgres Plus Advanced Server, which *does* have procedures. I suggest asking on the EnterpriseDB forums; they don't hang out and answer things here, and there's little activity on EDB-specific topics. – Craig Ringer May 09 '14 at 12:56
  • Now now @CraigRinger. I am sure that *I* am using PostgreSQL, even though the OP may not be. My minor poke in your eye being that the `CREATE TRIGGER` syntax uses the term `PROCEDURE` in order to specify a function name. – Patrick May 09 '14 at 12:59
  • 1
    You are correct, and sorry for misleading, but we are using an EnterpriseDB version. – Henry May 09 '14 at 13:35
  • @CraigRinger, thanks for helping me clarify this. I've edited things to read EnterpriseDB. I've also followed your advice and have posted on the EDB forums (don't know why I didn't think of that). – Henry May 09 '14 at 13:44
  • @Patrick Ah, whoops. I take your point . – Craig Ringer May 09 '14 at 14:19
  • @Henry please add a link to your edb forum post so others can find it later. – Craig Ringer May 09 '14 at 14:20
  • EnterpriseDB Forum Post: http://forums.enterprisedb.com/posts/list/3917.page – Henry May 09 '14 at 14:47

1 Answers1

1

Looks like I've found what I needed. There are views in the pg_catalog named pg_function and pg_procedure that make the distinction I need.

Henry
  • 600
  • 2
  • 7
  • 22