create stored procedure in postgre SQL and return as table

stored procedure on postgre sql

in sql programming are often found whose names are stored procedures.

Stored procedures are used by programmers because stored procedure is faster than query and to make programming easier.

in this sql tutorial, we will learn to how to create and use a stored procedure with easy example.

To create a new stored procedure, you use the CREATE PROCEDURE statement. You can create stored procedure in sql with parameters or without parameter.

The following shows the syntax of the CREATE PROCEDURE statement with some variable and then return some result in table format:

CREATE OR REPLACE FUNCTION procedure_name (variable_name1 date, variable_name2 character varying)
RETURNS TABLE ( column_name1 character varying, column_name2 character varying )
AS $$
BEGIN
RETURN QUERY select * from table_name where field_name1 = variable_name1 and field_name2 = variable_name2;
END; $$
LANGUAGE 'plpgsql';

In this syntax:

• First, specify the name of the stored procedure after the CREATE PROCEDURE clause (called procedure_name). 
• Next, define a parameter list which is similar to the parameter list of user-defined functions (we made two variable, variable_name1 and variable_name2). 
• Specify the type of returning result. We choose to return table with two column, column_name1 and column_name2. 
• After that, place the code in the return query of the stored procedure after that AS keyword. 
• Then, use double dollar ($$) to end the stored procedure. 
• Finally, specify the programming language for the stored procedure such as PLpgSQL and SQL.

So, what we have got here? the output will be showing table as result

column_name1	column_name2
data1	other_data1
data2	other_data2
data3	other_data3

Share this post