SQL to return all function parameters- its working

From: Postgres User <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: SQL to return all function parameters- its working
Date: 2009-05-29 22:40:14
Message-ID: b88c3460905291540o7961d537o91f95c3010303d30@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Based on replies to another post (recommending use of
'generate_series'), I was able to write the following query that
returns all paramters of a given function.
Only one interesting thing to note- in order to return the proper
argument type, I had to use
proargtypes[i - 1] when I expected this to work: proargtypes[i]

Any feedback would be appreciated...

SELECT n.nspname AS name_space,
p.proname AS function_name,
p.oid AS function_oid, t.typname AS rettype,
p.prosrc AS body,
argument, argument_type
FROM pg_proc p
INNER JOIN (SELECT oid, proargnames[i] AS argument, proargtypes[i-1]
AS argument_type
FROM
(SELECT oid, proargnames, proargtypes,
generate_series(1, array_upper(proargnames,1)) AS i
FROM pg_proc) s
) arg
ON p.oid = arg.oid
INNER JOIN pg_namespace n
ON p.pronamespace = n.oid
LEFT OUTER JOIN pg_type t
ON t.oid = p.prorettype
-- WHERE p.proname = 'func_name'

Browse pgsql-general by date

  From Date Subject
Next Message Scott Bailey 2009-05-29 23:34:31 Switching databases over JDBC/ODBC
Previous Message Postgres User 2009-05-29 22:36:14 Re: Converting each item in array to a query result row