pg sp exmple

pg_sp.sql

# psql -U postgres
psql (9.3.4)
Type "help" for help.

postgres=# CREATE FUNCTION insert_city(character varying)
postgres-#   RETURNS void AS
postgres-# $BODY$
postgres$# BEGIN
postgres$#  INSERT INTO city("name") VALUES($1);
postgres$# END
postgres$# $BODY$
postgres-#   LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
CREATE FUNCTION
postgres=# 
postgres=# 
postgres=# SELECT  proname
postgres-# FROM    pg_catalog.pg_namespace n
postgres-# JOIN    pg_catalog.pg_proc p
postgres-# ON      pronamespace = n.oid
postgres-# WHERE   nspname = 'public' // <---- we should iterate over all schemas ?
postgres-# ;
   proname   
-------------
 insert_city
(1 row)

postgres=# SELECT prosrc FROM pg_proc WHERE proname = 'insert_city';
                prosrc                 
---------------------------------------
                                      +
 BEGIN                                +
  INSERT INTO city("name") VALUES($1);+
 END                                  +
 
(1 row)

postgres=#