Get definition of function, sequence, type etc. in Postgresql with SQL query

To get the definition of a function use pg_get_functiondef():

select pg_get_functiondef(oid)
from pg_proc
where proname = 'foo';

There are similar functions to retrieve the definition of an index, a view, a rule and so on. For details see the manual: http://www.postgresql.org/docs/current/static/functions-info.html

Getting the definition of a user type is a bit more tricky. You will need to query information_schema.attributes for that:

select attribute_name, data_type
from information_schema.attributes
where udt_schema = 'public'
  and udt_name = 'footype'
order by ordinal_position;

From that you need to re-assemble the create type statement.

For more details you will need to read through the documentation of the system catalog: http://www.postgresql.org/docs/current/static/catalogs.html

But you should prefer information_schema views if they return the same information.

psql -E is instrumental for the task.

After invoking psql like this, it displays the queries used by backslash-commands - like \df+ myfunc for details about this function.

Or achieve the same in interactive mode by setting the special variable ECHO_HIDDEN:

\set ECHO_HIDDEN on

Here is a complete sample query using pg_get_functiondef:

WITH funcs AS (
  SELECT
    n.nspname AS schema
    ,proname AS sproc_name
    ,proargnames AS arg_names
    ,t.typname AS return_type
    ,d.description
    ,pg_get_functiondef(p.oid) as definition
  FROM pg_proc p
    JOIN pg_type t on p.prorettype = t.oid
    JOIN pg_description d on p.oid = d.objoid
    JOIN pg_namespace n on n.oid = p.pronamespace
  WHERE n.nspname = 'some_schema_name_here'
)
SELECT *
FROM funcs
;;

Note, you should obviously specify the schema name, (or "public" if you are using that schema)

You can also do \sf to see the user defined function in psql

SELECT
    pp.proname,                                     --function name.
    pp.pronamespace::regnamespace::text AS schema,  --function located schema
    pg_get_functiondef(oid),                        --function def
    pg_get_function_arguments(oid),                 --(including default values).
    pg_get_function_identity_arguments(oid),        --This form omits default values.
    pg_get_function_result(oid)                     --Reconstructs the RETURNS clause of a function
FROM
    pg_proc pp
WHERE
    proname = 'eval_safe';

from manual

pg_get_functiondef ( func oid ) → text   

Reconstructs the creating command for a function or procedure. (This is a decompiled reconstruction, not the original text of the command.) The result is a complete CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE statement.

pg_get_function_arguments ( func oid ) → text

Reconstructs the argument list of a function or procedure, in the form it would need to appear in within CREATE FUNCTION (including default values).

pg_get_function_identity_arguments ( func oid ) → text

Reconstructs the argument list necessary to identify a function or procedure, in the form it would need to appear in within commands such as ALTER FUNCTION. This form omits default values.

pg_get_function_result ( func oid ) → text

Reconstructs the RETURNS clause of a function, in the form it would need to appear in within CREATE FUNCTION. Returns NULL for a procedure.