Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The following query seems to work, but I am not sure that it is the best/always correct way:

select 
parameter_name, 
CASE WHEN data_type='ARRAY' 
    THEN SUBSTRING(udt_name, 2)||'[]' 
    ELSE data_type 
END AS data_type
from information_schema.parameters 
where specific_schema='my_schema'
and parameter_mode='IN'
and specific_name like 'my_proc_16831'
order by ordinal_position

Anyway, it looks kind of hacky to me. Is there a better way to retrieve, for example, numeric[] as a type if my parameter is an array of numeric?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

I think you want format_type(typid oid, typmod integer).

Given:

CREATE SCHEMA my_schema;

CREATE FUNCTION my_schema.my_proc_16831(numeric[])
RETURNS numeric[] LANGUAGE sql AS $$
SELECT $1;
$$;

You can get proc details with:

SELECT
  p.parameter_name,
  format_type((p.udt_schema||'.'||p.udt_name)::regtype, -1) 
FROM information_schema.parameters AS p
INNER JOIN information_schema.routines 
  USING (specific_catalog, specific_schema, specific_name) 
WHERE specific_schema = 'my_schema'   
  AND parameter_mode = 'IN'
  AND routine_name = 'my_proc_16831';

... though this removes the portability of using information_schema, since you're relying on Pg specific behaviour. It also assumes the typmod is always -1 because PostgreSQL doesn't keep track of the typmod for functions - e.g. numeric(18,2) becomes just numeric as a function argument.

At least this way you don't need to use your LIKE hack, though, or relying on changeable assumptions about array internal type names.

BTW, you can use pg_proc and pg_type directly instead. This might be worth doing if performance is critical, though otherwise information_schema will be more cross-version portable.

That gives you the equivalent:

SELECT 
  proargnames[argpos] AS arg_name, 
  format_type(proargtypes[argpos],-1) AS arg_type 
FROM pg_proc p 
INNER JOIN pg_namespace n ON (p.pronamespace = n.oid) 
CROSS JOIN LATERAL generate_subscripts(proargtypes, 1) argpos
WHERE n.nspname = 'my_schema'
  AND p.proname = 'my_proc_16831' 
/* AK: suggested additional clause
      AND argpos > 0
*/
    ORDER BY p.proname, argpos;
share|improve this answer
    
Craig, your first query works for me. Your second one returns one extra row with empty arg_name and argpos=0. What does that row mean? –  A-K Dec 31 '13 at 13:55
    
Also I added AND parameter_mode = 'IN', so that it works for functions which return data. –  A-K Dec 31 '13 at 17:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.