Introduction to PostgreSQL EXECUTE
PostgreSQL EXECUTE statement is used to execute the previously created prepared statement, to execute that statement using execute command we need to give the name of prepared statement and the parameter. Prepare statement in PostgreSQL only exist duration of the current session which we have used, after session disconnection prepare statement will automatically remove from the database server. We can use select, delete and insert statement using execute command in prepared statement.
Syntax:
Below is the syntax :
Execute (name of prepared statement) [(parameter)]
Parameter
Below is the parameter description syntax of EXECUTE statement in PostgreSQL:
- Execute: This statement is used to execute the prepared statement in PostgreSQL.
- Name: This is defined as name of prepared statement which was used with execute statement in PostgreSQL.
- Parameter: This is defined as actual value of the parameter prepared statement. This is the data type value which was compatible with the parameter of prepared statement in PostgreSQL.
How PostgreSQL EXECUTE Statement works?
Below is the working of EXECUTE statement :
- To use the execute statement in PostgreSQL we need first to create prepared statement. Without creating prepared statement we cannot use the execute statement.
- It will return the syntax error while executing the statement.
In the below example we need to first create 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 above first line of code we have used execute statement with exe_test prepare statement but it will show the error.
- So we need to create the prepared statement to use the execute statement.
- In second line of code we have successfully executed the execute statement because with this statement we have used prepared statement.
- We have created prepared statement name as exe_test, after creating the same we have used in execute statement.
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:
- In above code we have created prepared statement name as exe_test after creating the same we have use this using execute statement.
- After executing this we have disconnected from the session and, we are again trying to login with same user credentials.
- By following above steps we have again connected to the session but prepared statement will not exist into the database because it will automatically delete from the server after disconnecting from the current session.
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.
- In the example first we have created prepared statement name as exe_test.
- Using this prepared statement we are inserting data or rows into the exe_test table by using execute command.
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.
- In the example we have created prepared statement name as exe_test1. Using this prepared statement we are selecting the data from exe_test table by using execute command.
Code:
PREPARE exe_test1 (int, text, text) AS SELECT id, name, address FROM exe_test;
EXECUTE exe_test1(1, 'ABC', 'Mumbai');
Output:
Example #3
Delete data from the table by using execute statement.
Below example shows how to delete the data from the table by using execute statement in PostgreSQL.
- In the example we have created prepared statement name as exe_test2. Using this prepared statement we are deleting the data from exe_test table by using execute command.
Code:
PREPARE exe_test2 (int) AS delete from exe_test where id =1;
EXECUTE exe_test2(1);
select * from exe_test;
Output:
Recommended Articles
This is a guide to PostgreSQL EXECUTE. Here we discuss the introduction to PostgreSQL EXECUTE , how does the statement works along with query examples. You may also have a look at the following articles to learn more –
