How to show stored procedure code in PostgreSQL using SQL query in PgAdmin 4

Use this system table "pg_proc" to get the procedure source and other details from PgAdmin 4 query console.

SELECT prosrc FROM pg_proc WHERE proname = 'procedurename'

The above quey does not display the complete proc source including the CREATE FUNCTION. For that we need to use system function "pg_get_functiondef()". This function display entire procedure source.

SELECT pg_get_functiondef((SELECT oid FROM pg_proc WHERE proname='procedurename')
		            );

Shortcut to show PostgreSQL function code in PgAdmin

What is the command to find script of a existing function in postgresql? The answer is pg_get_functiondef(). As we are utilizing sp_helptext [procedure name] to get the SP script in Sql server, In postgresql using this pg_get_functiondef() command, we can enable macro for using shortcut keys in Postgre SQL PgAdmin III. For enable macro open PostgreSQL query window select the "Manage macros" menu from "Macros".

We can use any key in the macro section. If we want to use shortcut key as Ctrl+1, simply click on the Ctrl+1 key in macro then give a name for the macro and copy and paste the below mentioned code. After saving the macro you can use Ctrl+1 to display the full Postgre SQL function code.

DO
$$
DECLARE v_Inputtext TEXT;
        v_output text;

BEGIN
        v_Inputtext    := (SELECT lower('$SELECTION$'));
        v_output       := (SELECT pg_get_functiondef(oid) FROM pg_catalog.pg_proc WHERE proname = v_Inputtext);
         
        RAISE NOTICE '%',v_output;


END;
$$

Please visit other related articles

PostgreSQL: How to Create PostgreSQL Index and Postgres Create Index examples

PostgreSQL: Postgres list tables using PSQL

PostgreSQL: How to convert PostgreSQL multiple rows to json

PostgreSQL: Script to drop foreign keys in a table
PostgreSQL: Script to List Functions in PostgreSQL DB

PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to display full PostgreSQL function code

PostgreSQL: Enable macro in PgAdminIII
PostgreSQL: Export PostgreSQL data in to excel file
PostgreSQL: How to restrict the user access to the databases in Postgresql based on the permission
PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to get ROW_COUNT of last executed query in PostgreSQL like MS SQL @@ROWCOUNT ?

How to list functions in PostgreSQL database?

Using the below mentioned query we can list all functions in a PostgreSQL database.

SELECT  DISTINCT routines.routine_name
FROM    information_schema.routines
WHERE   routines.specific_schema='public'
ORDER BY routines.routine_name;

Please visit other related articles

PostgreSQL: How to Create PostgreSQL Index and Postgres Create Index examples

PostgreSQL: Postgres list tables using PSQL

PostgreSQL: How to convert PostgreSQL multiple rows to json

PostgreSQL: Script to drop foreign keys in a table
PostgreSQL: Script to List Functions in PostgreSQL DB

PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to display full PostgreSQL function code

PostgreSQL: Add macros in PgAdminIII
PostgreSQL: Export PostgreSQL data in to excel file
PostgreSQL: How to restrict the user access to the databases in Postgresql based on the permission
PostgreSQL: List table details using Alt+F1 in PostgreSQL
PostgreSQL: How to get ROW_COUNT of last executed query in PostgreSQL like MS SQL @@ROWCOUNT ?