how to display full stored procedure code

Question

How do you view a stored procedure function   Say I have an old function without the original definition - I want to see what it is doing in pg psql but I can t seem to figure out a way to do that   using Postgres version 8 4 1

User · Answer

SELECT prosrc FROM pg proc WHERE proname    function name     This tells the function handler how to invoke the function  It might be the actual source code of the function for interpreted languages  a link symbol  a file name  or just about anything else  depending on the implementation language call convention

User · Answer

To see the full code query  written in stored procedure  functions  Use below Command   sp helptext procedure function name   for function name and procedure name don t add prefix  dbo   or  sys     don t add brackets at the end of procedure or function name and also don t pass the parameters   use sp helptext keyword and then just pass the procedure  function name   use below command to see full code written for Procedure   sp helptext ProcedureName   use below command to see full code written for function   sp helptext FunctionName

User · Answer

df   lt function name gt  in psql

User · Answer

ef  lt function name gt  in psql  It will give the whole function with editable text

User · Answer

You can also get by phpPgAdmin if you are configured it in your system   Step 1  Select your database  Step 2  Click on find button  Step 3  Change search option to functions then click on Find   You will get the list of defined functions You can search functions by name also  hope this answer will help others

User · Answer

Use  df to list all the stored procedure in Postgres

User · Answer

If anyone wonders how to quickly query catalog tables and make use of the pg get functiondef   function here s the sample query   SELECT n nspname AS schema        proname AS fname        proargnames AS args        t typname AS return type        d description        pg get functiondef p oid  as definition --       CASE WHEN NOT p proisagg THEN pg get functiondef p oid  --            ELSE  pg get functiondef   can  t be used with aggregate functions  --       END as definition   FROM pg proc p   JOIN pg type t     ON p prorettype   t oid   LEFT OUTER   JOIN pg description d     ON p oid   d objoid   LEFT OUTER   JOIN pg namespace n     ON n oid   p pronamespace  WHERE NOT p proisagg    AND n nspname   lt  SCHEMA NAME PATTERN gt      AND proname   lt  FUNCTION NAME PATTERN gt

User · Answer

use pgAdmin or use pg proc to get the source of your stored procedures  pgAdmin does the same

User · Answer

Normally speaking you d use a DB manager application like pgAdmin  browse to the object you re interested in  and right click your way to  script as create  or similar   Are you trying to do this    without a management app

[postgresql] how to display full stored procedure code?

Examples related to postgresql

Examples related to stored-procedures