I can share my project, a set of AWS Lambda based user defined functions (UDF) that allow one to do direct/reverse geocoding using SQL statements, delegating this task to the newly released Amazon Location Service. Check it out on:

https://github.com/aws-samples/amazon-redshift-location-user-defined-functions.

The project contain all the source code to setup the UDFs, the IAM roles, the AWS Lambdas in a CloudFormation template. You can even Launch it in your account clicking on the "Lauch Stack" button.

It's a completely functional sample, please check.

Found the answer:

select * from pg_proc where proname ilike '%<function_name>%'

In the column: prosrc you can find the function definition. It seemed empty in SQLWorkbenchJ, but the code revealed itself after a double-click.

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