PostgreSQL EXECUTE

Execute (name of prepared statement) [(parameter)]

Parameter

Below is the parameter description syntax of EXECUTE statement in PostgreSQL:

How PostgreSQL EXECUTE Statement works?

Below is the working of EXECUTE statement :

In the below example, we need first to create a prepared statement.

Code:

EXECUTE exe_test(1, 'ABC', 'Mumbai');
PREPARE exe_test (int, text, text) AS INSERT INTO exe_test VALUES($1, $2, $3);
EXECUTE exe_test(1, 'ABC', 'Mumbai');
select * from exe_test;

Output:

In the next example, we will see the prepare statement is only valid in the current session, which we have connected, after disconnecting from the session prepare statement is automatically removed from the database server.

Code:

PREPARE exe_test (int, text, text) AS INSERT INTO exe_test VALUES($1, $2, $3);
EXECUTE exe_test(1, 'ABC', 'Mumbai');
select * from exe_test;
psql -U postgres
EXECUTE exe_test(1, 'ABC', 'Mumbai');

Output:

Examples of PostgreSQL EXECUTE

Given below are the examples mentioned :

Example #1

Insert data into the table by using execute statement.

Below example shows how to insert the data into the table by using execute statement in PostgreSQL.

Code:

PREPARE exe_test (int, text, text) AS INSERT INTO exe_test VALUES($1, $2, $3);
EXECUTE exe_test(2, 'PQR', 'Delhi');
EXECUTE exe_test(3, 'XYZ', 'Pune');
select * from exe_test;

Output:

Example #2

Select data from the table by using execute statement.

Below example shows how to select the data from the table by using execute statement in PostgreSQL.

Code:

PREPARE exe_test1 (int, text, text) AS SELECT id, name, address FROM exe_test;
EXECUTE exe_test1(1, 'ABC', 'Mumbai');

PREPARE exe_test2 (int) AS delete from exe_test where id =1;
EXECUTE exe_test2(1);
select * from exe_test;