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 enable macro in PgAdminIII for using shortcut keys

We can enable macro for using shortcut keys in PostgreSQL PgAdminIII. For enable macro open PostgreSQL query window select the "Manage macros" menu from "Macros"

When we select the "Manage macros" a pop-up window will be displayed like below.

Give the details then save.

Example for PgAdmin keyboard shortcuts using macro.

ALT + F1

CTRL + 1

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 ?

How to get PostgreSQL column list and datatypes of that table? How Postgres get column names works using macro?

From the following sql query we can get the all column names and datatype of table in PostgreSQL. Postgres get column names select the column names from the information_schema.columns system table.

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME';                                         

How to add macro in PgAdminIII for using Alt+F1 shortcut key | Postgres show tables details using ALT F1

In Microsoft SQL Alt+F1 using for table information. Like using Alt+F1 in PostgreSQL we can get the details of a table by adding the macros. We can use macros for using shortcut keys in PostgreSQL PgAdminIII. For adding macros open PostgreSQL query window select the "Manage macros" menu from "Macros"

When we select the "Manage macros" a pop-up window will be displayed like below.

Click on Alt+F1, give a name for the macro and copy and paste the below displayed sql query.

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = LOWER('$SELECTION$');                                

Then save the details

Select a table and press Alt+F1, you will get the details of that table like above displayed image. Postgres get column names.

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 ?