Viewing packages and package body definition v15

Suggest edits

You can view the package specification and package body definition using the psql meta-commands \sps and \spb, respectively.

Synopsis

\sps[+] [<schema_name>].<package_name>
\spb[+] [<schema_name>].<package_name>

Creating and viewing a package and a package body

Create a package and a package body test_pkg in the public schema:

edb=# CREATE OR REPLACE PACKAGE public.test_pkg IS
edb$# emp_name character varying(10);
edb$# PROCEDURE get_name(IN p_empno numeric);
edb$# FUNCTION display_counter() RETURN integer;
edb$# END;
CREATE PACKAGE
edb=#
edb=# CREATE OR REPLACE PACKAGE BODY public.test_pkg IS
edb$# v_counter integer;
edb$# 
edb$# PROCEDURE get_name(IN p_empno numeric) IS
edb$# BEGIN
edb$# SELECT ename INTO emp_name FROM emp WHERE empno = p_empno;
edb$# v_counter := v_counter + 1;
edb$# END;
edb$# 
edb$# FUNCTION display_counter() RETURN integer IS
edb$# BEGIN
edb$# RETURN v_counter;
edb$# END;
edb$# BEGIN
edb$# v_counter := 0;
edb$# DBMS_OUTPUT.PUT_LINE('Initialized counter');
edb$# END;
CREATE PACKAGE BODY
edb=#

Use \sps and \spb commands to view the definition of package and package body:

edb=# \sps test_pkg
CREATE OR REPLACE PACKAGE public.test_pkg IS
emp_name character varying(10);
PROCEDURE get_name(IN p_empno numeric);
FUNCTION display_counter() RETURN integer;
END
edb=#
edb=# \sps+ test_pkg
1       CREATE OR REPLACE PACKAGE public.test_pkg IS
2       emp_name character varying(10);
3       PROCEDURE get_name(INOUT p_empno numeric);
4       FUNCTION display_counter(OUT p1 numeric, OUT p2 numeric) RETURN integer;
5       END

edb=# \sps public.test_pkg
CREATE OR REPLACE PACKAGE public.test_pkg IS
emp_name character varying(10);
PROCEDURE get_name(INOUT p_empno numeric);
FUNCTION display_counter(OUT p1 numeric, OUT p2 numeric) RETURN integer;
END

edb=# \sps+ public.test_pkg
1       CREATE OR REPLACE PACKAGE public.test_pkg IS
2       emp_name character varying(10);
3       PROCEDURE get_name(INOUT p_empno numeric);
4       FUNCTION display_counter(OUT p1 numeric, OUT p2 numeric) RETURN integer;
5       END

edb=# \spb test_pkg
CREATE OR REPLACE PACKAGE BODY public.test_pkg IS
v_counter integer;

PROCEDURE get_name(IN p_empno numeric) IS
BEGIN
SELECT ename INTO emp_name FROM emp WHERE empno = p_empno;
v_counter := v_counter + 1;
END;

FUNCTION display_counter() RETURN integer IS
BEGIN
RETURN v_counter;
END;
BEGIN
v_counter := 0;
DBMS_OUTPUT.PUT_LINE('Initialized counter');
END
edb=#

Viewing function and procedure definitions

You can also view the definition of individual functions and procedures using the \sf command.

Create the function and procedure:

edb=# CREATE OR REPLACE FUNCTION public.func1()
edb-#  RETURNS integer
edb-#  LANGUAGE edbspl
edb-#  SECURITY DEFINER
edb-# AS $function$ begin return 10; end$function$;
CREATE FUNCTION
edb=#
edb=# CREATE OR REPLACE PROCEDURE public.proc1()
edb-#  SECURITY DEFINER
edb-# AS $procedure$ begin null; end$procedure$
edb-#  LANGUAGE edbspl;
CREATE PROCEDURE
edb=#

Use the \sf <function_name/procedure_name> command to view the definition:

edb=# \sf func1
CREATE OR REPLACE FUNCTION public.func1()
 RETURNS integer
 LANGUAGE edbspl
 SECURITY DEFINER
AS $function$ begin return 10; end$function$
edb=# 
edb=# \sf proc1
CREATE OR REPLACE PROCEDURE public.proc1()
 SECURITY DEFINER
AS $procedure$ begin null; end$procedure$
 LANGUAGE edbspl
edb=#

← Prev

Package components

↑ Up

Working with packages

Creating packages