SHOW CREATE PROCEDURE

Function

It returns the exact string that can be used to recreate the named procedure. A similar statement, SHOW CREATE FUNCTION, displays information about the stored function. To use either statement, you must have global SELECT privileges.

Precautions

sql_mode is the session value for the query. bDatabase shows here the sql_mode bound when the routine is created, MogDB shows here the session value because MogDB does not bind the routine to sql_mode when it creates the routine.

character_set_client is the session value of the system variable for client_encoding when the routine was created.

collation_connection is the value specified when lc_collate creates the database.

Database Collation is the value specified by lc_collate when it created the database.

Syntax

SHOW CREATE PROCEDURE proc_name

Parameter Description

proc_name

Stored procedure name.

Examples

-- Create a stored procedure.
MogDB=# create procedure test_procedure_test(int,int)
MogDB-# SHIPPABLE IMMUTABLE
MogDB-# as
MogDB$# begin
MogDB$# select $1 + $2;
MogDB$# end;
MogDB$# /
CREATE PROCEDURE
-- Query stored procedure creation statements.
MogDB=# show create procedure test_procedure_test;
      Procedure      |                        Create Procedure                         |              sql_mode               | character_set_client | collation_connection | Database Collation 
---------------------+-----------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
 test_procedure_test | CREATE OR REPLACE PROCEDURE public.test_procedure_test(int,int)+| sql_mode_strict,sql_mode_full_group | UTF8                 | en_US.UTF-8    
      | en_US.UTF-8
                     |  IMMUTABLE SHIPPABLE                                           +|                                     |                      |                
      | 
                     | AS  DECLARE                                                    +|                                     |                      |                
      | 
                     | begin                                                          +|                                     |                      |                
      | 
                     | select $1 + $2;                                                +|                                     |                      |                
      | 
                     | end;                                                           +|                                     |                      |                
      | 
                     | /                                                              +|                                     |                      |                
      | 
                     |                                                                 |                                     |                      |                
      | 
(1 row)