CODEWITHSUNDEEP
postgresql
edg
edg

Reputation: 669

PostgreSQL how to check if parameter exists in a function

In SQL Server, I can validate if a specific parameter exists in a given Stored Procedure like so:

SELECT [name]
FROM sys.parameters 
WHERE object_id = object_id('MyStoredProc')
AND [name]='@paramenter_im_checking'

Is there an equivalent in PostgreSQL?

Upvotes: 0

Views: 2635

Answers (2)

edg
edg

Reputation: 669

It turned out I also need to check the schema in case the same function name exists in more than one Schemas.

Here's the updated query:

SELECT 'parameter_name' = ANY (proargnames)
FROM pg_catalog.pg_proc p INNER JOIN pg_catalog.pg_namespace n 
ON n.oid = p.pronamespace
WHERE n.nspname='schema_name'
AND proname = 'function_name';

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246308

You can query the system catalog pg_proc for named parameters of a function or procedure:

SELECT 'parameter_name' =ANY (proargnames)
FROM pg_catalog.pg_proc
WHERE proname = 'function_name';

This will return TRUE or FALSE, depending on whether the function or procedure has a parameter of that name or not.

Upvotes: 1

Related Questions