PostgreSQL function data types

Question

How can I get the names and data types of all variables usedin in PostgreSQL functions? Going through prosrc column in pg_proc is one way. Is there a better one - some view which stores the information in a format that can be easily queried/filtered? PostgreSQL Version is 9.0.

Do you mean inside the functions, not just the parameters passed into them? — Sami Kuhmonen
– Sami Kuhmonen, Commented May 15, 2015 at 5:38
Yes, inside the functions. Arguments I can get using pg_get_function_arguments — Jayadevan
– Jayadevan, Commented May 15, 2015 at 5:57

Patrick · Accepted Answer · 2015-05-17 05:56:35Z

2

No, there is no in-built way in PostgreSQL to do this. The function body is stored as a text string without parsing it out into its components. You can - obviously - parse the variable names and data types out, but it requires some serious parsing.

For PL/pgSQL:

Read the first word (i.e. the first term separated by white-space from the next term); if it is declare then read on until the word begin.
Read the next word, this is the name of a variable.
Read everything until a comma , (separating one variable from another) or the word begin (end of variables), but ignore commas inside parentheses () as these are associated with an initialization of the variable or a data type modifier (such as numeric(8, 2)). This is the data type, with all of it's modifiers and any initialization.
From the text read in the previous step, extract everything up to :=. Everything before is the data type and any modifiers, everything after is an initial value.
Repeat from step 2 until you encounter the word begin.

There may be nested blocks in a single procedure, so you'd have to repeat this procedure for every block in the body.

For other languages like PL/Perl, PL/Tcl and PL/Python you'd have to parse variables according to that grammar. And then there are additional modules with procedural languages, like PL/R or PL/Sh, with yet again different grammar.

This is obviously not done with a simple reg_exp in a query, but would require some more advanced logical processing.

edited May 17, 2015 at 5:56

answered May 15, 2015 at 8:04

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

2 Comments

Nick Barnes Over a year ago

You can nest DECLARE/BEGIN/END blocks, so it could get considerably worse than this...

Sami Kuhmonen Over a year ago

This also works only for PL/PgSQL functions. If there are PL/Perl, PL/R or other functions, their variable definitions are different.

Collectives™ on Stack Overflow

PostgreSQL function data types

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related