Question 302270 in PostgreSQL Question posted in
The official documentation can be found here.

How can I use a query to determine if a function is scalar in postgresql?

I can’t figure out how to get a list of scalar functions in a postgres database.

I’ve tried to write different queries, but I can’t find an exact solution to determine the scalar type returned by a function.
So far the only solution that has come to mind is filtering the name of the function return type with a regular expression.

Tags:

2

Answers


  1. 0

    You can use INFORMATION_SCHEMA.ROUTINES to get the datatype of the functions.

    Adding the datatype to the where clause will give you all functions.

    But the returned rows are only form the current Schame/databse.

    SELECT specific_name,routine_name,data_type  
FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = 'FUNCTION';
    Login or Signup to reply.
  2. 0

    If by scalar you mean non-set-returning, system catalog pg_proc column proretset can tell you that: demo

    select oid::regprocedure, prorettype::regtype 
from pg_proc where not proretset and prokind in ('a','f','w');

    ('a','f','w') mean aggregate, regular and window functions. It’s worth taking a look at return types because with the definition assumed above, a function that returns void, returns trigger, returns cursor is also scalar.

    The standard information_schema.routines does not differentiate between scalar and non-scalar functions in any way.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search