Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get a list of UDFs in Redshift?

Is there an easy way to get the list of all UDFs that are available in Redshift? Moreover, I would like to find UDFs with parameter types and search for UDFs by name.

like image 788
volodymyr Avatar asked Oct 12 '15 14:10

volodymyr


1 Answers

You can query the pg_proc table to get all the UDFs available.

Filter by name

You can filter by name using the proname column:

SELECT * FROM pg_proc WHERE proname ILIKE '%<name_here>%';

Filter by parameter type

You can filter by parameter types using the proargtypes column:

SELECT * FROM pg_proc WHERE proargtypes = 1043;

Here, 1043 is varchar as can be seen by querying the pg_type table:

SELECT * FROM pg_type WHERE typname ILIKE '%char%';

Filter by parameter name

You can also filter by parameter name using the proargnames column:

SELECT * FROM pg_proc WHERE proargnames = ARRAY['foo'];

References:

http://docs.aws.amazon.com/redshift/latest/dg/c_join_PG.html

http://www.postgresql.org/docs/8.0/static/catalog-pg-proc.html

like image 91
DotThoughts Avatar answered Oct 16 '22 21:10

DotThoughts