PostgreSQL how to check if parameter exists in a function

Question

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?

Not that I know of. But it seems nonsensical to check for the existence of a parameter that you declared in the same function - this is static information that cannot change at run time. — Laurenz Albe
– Laurenz Albe, Commented Nov 15, 2018 at 6:10
I simplified the example, my real use case is that the stored procedure to check for parameter existence is not static.. it's coming from a setup table — edg
– edg, Commented Nov 15, 2018 at 15:16
Your parameter names don't come from a lookup table, they are fixed. So just repeat the parameter name in the function body. — Laurenz Albe
– Laurenz Albe, Commented Nov 15, 2018 at 16:02
@LaurenzAlbe - The actual query looks like... " WHERE object_id=object_id(@SomeStoredProc) ". The SomeStoredProc can be any stored procedure. That stored proc may or may not have the parameter I am looking for. — edg
– edg, Commented Nov 15, 2018 at 17:16

Laurenz Albe · Accepted Answer · 2018-11-15 17:29:04Z

1

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.

answered Nov 15, 2018 at 17:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

edg Over a year ago

Exactly what I was looking for! Thanks!

edg · Accepted Answer · 2018-11-15 18:39:31Z

0

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';

answered Nov 15, 2018 at 18:39

edg

6697 silver badges13 bronze badges

Collectives™ on Stack Overflow

PostgreSQL how to check if parameter exists in a function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related