CODEWITHSUNDEEP
sqlfunctionpostgresqlstored-procedures
Rishi Rahiman
Rishi Rahiman

Reputation: 312

how to get the list of arguments of a stored procedure in postgresql

I want to get the list of arguments of a stored procedure in postgresql, is there any query to get the arguments ?

I got one system procedure to do the same, which is :

SELECT pg_get_function_identity_arguments('funcname'::regproc);

But here when there are more than one functions with same name then it will return an error.

Any solutions??

Thanks in advance..

Upvotes: 1

Views: 5036

Answers (3)

Durja
Durja

Reputation: 667

Here is a clean version:

SELECT 
proname,
unnest(proargnames) as arguments, 
unnest(string_to_array((oidvectortypes(proargtypes)), ',')) as 
arguments_type
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p   ON    pronamespace = n.oid     
JOIN    pg_type t ON p.prorettype = t.oid  
WHERE   nspname = 'public'
group by proname, proargtypes, proargnames;

Upvotes: 0

Alex Yu
Alex Yu

Reputation: 3537

It's too late for OP but anyway: I searched answer today myself and did not found needed answer.

I came up with this solution:

SELECT 
        pronamespace::regnamespace,
        proname,
        pg_get_function_arguments(oid) AS args_def, -- full definition of arguments with defaults
        UNNEST(string_to_array(pg_get_function_identity_arguments(oid), ',' )) AS arg -- each argument with type
    FROM 
        pg_proc

Works in postgresql 11.x, I suppose that must work in other versions too but not tested.

Hope this helps somebody in future.

Upvotes: 2

Michał Niklas
Michał Niklas

Reputation: 54292

For my tool that reports PostgreSQL schema I use something like:

SELECT CASE
        WHEN pg_proc.proretset
        THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)
        ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END,
    pg_proc.proargtypes,
    pg_proc.proargnames,
    pg_proc.prosrc,
    pg_proc.proallargtypes,
    pg_proc.proargmodes,
    pg_language.lanname
FROM pg_catalog.pg_proc
    JOIN pg_catalog.pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid)
    JOIN pg_catalog.pg_language ON (pg_proc.prolang = pg_language.oid)
WHERE
    pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
    AND (pg_proc.proargtypes[0] IS NULL
        OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
    AND NOT pg_proc.proisagg
    AND pg_proc.proname ilike 'MY_FUNCTION_NAME'
    AND pg_namespace.nspname = 'public'
    AND pg_catalog.pg_function_is_visible(pg_proc.oid);

See 2nd and 3rd column of its result.

Upvotes: 0

Related Questions