Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Working with Stored Procedures in PostgreSQL

In this article we will see a brief overview around working with stored procedures with PostgreSQL.

One of the most used features by developers in databases is the Stored Procedure, because it concentrates the logic required for certain functions, thus having greater flexibility in the return of important information. Behold, our subject will be on the creation and execution of stored procedures with the DBMS, present in its most stable version 9.4 (up to the time of writing this article).

By working with ORDBMS (Object Relational Database Management System) PostgreSQL have not necessarily the function Stored Procedure (SP) defined, as we can find in many other types of DBMS. They are, in fact, small pieces of code stored at server-side of a database. Contrary to what happens in other databases, the SPs in Postgres are defined as FUNCTIONS, as well as triggers, making this feature a bit more complicated, depending on its return type. These functions are important and different features, but created equal. Working with the creation of these small snippets of code is, in a way, a good practice because it can leave quite complex code acting on the server side that can be used by multiple applications, thus avoiding the need to replicate them in each of these applications.

The Stored Procedures are defined into three distinct types:

  • Non-Procedural Languages - are languages that do not require writing a traditional programming logic. In this case, users focus to define the input and output of information, instead of the steps of the program required in a procedural programming language, such as C ++ or Java. They use the SQL as a language, but havecommonstructures to programming languages, such as the use of repetition structures;
  • Procedural - procedural languages are programming languages that specify a series of steps and procedures well structured within your programming environment. They have a systematic order of statements, functions and commands to complete the tasks. The best known among them is the PL/pgSQL.
  • Foreign languages - are functions usually written in C ++ programming language, which is advantageous when using the language with several resources where we can implement algorithms with greater complexities. These functions can be registred and packaged in DBMS for future use.

Listing 1 presents the basic syntax of a SP.

Listing 1. Basic syntax creating FUNCTIONS/STORED PROCEDURES.

CREATE [ OR REPLACE ] FUNCTION
      name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
      [ RETURNS rettype
        | RETURNS TABLE ( column_name column_type [, ...] ) ]
    { LANGUAGE lang_name
      | WINDOW
      | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
      | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
      | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
      | COST execution_cost
      | ROWS result_rows
      | SET configuration_parameter { TO value | = value | FROM CURRENT }
      | AS 'definition'
      | AS 'obj_file', 'link_symbol'
    } ...
      [ WITH ( attribute [, ...] ) ]

The syntax begins with the creation of a new function with the CREATE FUNCTION expression, followed by the name to define the name that will be created. Then some parameters are displayed:

  • Argmode - this parameter defines the mode argument, which can be IN, OUT, INOUT orVARIADIC. By default, theparameter used is IN;
  • ArgName - here we define the name of an argument;
  • Argtype - we define the data type of function arguments, which can be base, composite, or domain, or even reference the type of a table column;
  • default_expr - here we can use a default value if the parameter has not been specified. The expression must be coercible for the argument type of the parameter, namely the corresponding values using the same internal representation;
  • rettype - is the return type of data. When there are OUT or INOUT parameters, the RETURNS clause can be omitted. The SETOF modifier indicates that the function will return a set of items, rather than a single item;
  • column_name - is the name of an output column in the RETURNS TABLE syntax;
  • column_type - data type of an output column in the RETURNS TABLE syntax;
  • lang_name - is the name of the language in which the function is implemented, which may be SQL, C, or the name of a user-defined procedural language;
  • WINDOW - This attribute indicates that the function is a window function rather than being a simple function. This is useful for functions written in C language and can not be changed by replacing the existing definition function;
  • IMMUTABLE, STABLE, VOLATILE - These attributes inform the query optimizer about the behavior of the function. If none set, VOLATILE is the default option, ie can do anything, including database modification. Already STABLE can't modify the database and returns the same arguments for all rows within a single statement, as IMMUTABLE also can modify the database and returns the same arguments forever;
  • CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT, STRICT - the CALLED ON NULL INPUT is used by default to indicate that the function is called normally when some of its arguments is null. The other two types indicate that the function always returns null whenever any of its arguments are null.
  • [EXTERNAL] SECURITY INVOKER, [EXTERNAL] SECURITY DEFINER - the SECURITY INVOKER indicates that the function should be executed with the privileges of the user calls, which is set by default. Already SECURITY DEFINER specifies that the function should be executed with the privileges of the user that created it;
  • result_rows - returns a number of lines as a result of the function.

The main advantage of using stored procedures is the reduction of the number of requests made by the application server to the database, in order to have the SQL statements issued by a single function call to obtain the expected result therefore have increasing application performance, besides the possible reuse of the same code in different applications.

To exemplify the types of returns possible for Stored Procedures in PostgreSQL, we will see below some practical examples. In the first example we have a Stored Procedure containing the word "void", as we see according to Listing 2.

Listing 2. Creating a stored procedure that returns no value.

CREATE OR REPLACE FUNCTION InsertEmployee(code INTEGER, name VARCHAR(100), email VARCHAR(150), phone VARCHAR(15), city VARCHAR(50), state VARCHAR(2))


      RETURNS void AS $
      BEGIN
        INSERT INTO "MyCompany".tb_employee VALUES (code, name, email, telephone, city, state);
      END;
      $ LANGUAGE 'plpgsql';

To create our procedure we use the term "void", which sets the return without submitting messages. With our SP created we will perform an insertion test to see it up and running, we can do it using the following statement:

SELECT InsertEmployee(5, 'Jhon Mac', 'jhon.mac@gmail.com', '+1 33 323223', 'Rainbow Avenue 12', 'NY');

After the new record insertion we can do a SELECT to verify that the information was added correctly using the following statement:

SELECT code, name, email, phone, city, state
    FROM "MyCompany".tb_employee;

When we declare a PL/pgSQL composed of output parameters, these will be passed optionally with first and last names, such as $Return, exactly the same way as the normal input parameters. An output parameter is a variable that starts out NULL and should be allocated for the execution of the function. The final value of the parameter is what is returned, as we can see an example of mathematical operations in Listing 3.

Listing 3. Returning results with output parameters.

CREATE FUNCTION mathCalcs(x int, y int, OUT sum int, OUT subtract int, OUT multiplication int, OUT division int) AS $
BEGIN
    sum := x + y;
    subtract := x - y;
    multiplication := x * y;
    division := x / y;
END;
              
$ LANGUAGE plpgsql;

In this case we use the OUT command, that gives the output of the results obtained from the input values x and y. Another way to obtain results in the SP's is returning a function as a TABLE, like in Listing 4.

Listing 4. Returning functions as tables.

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
            
$ LANGUAGE plpgsql;

The fields in a table can be generated with new information in a table apart, without the need to create new fields or use other functions to have the same result.

Conclusion

With this we finish our article, in which we dealt with some important points concerning the creation and use of stored procedures, and we know what we can do with them or not without have a bad writing and bad understanding code. In discussing safety in our code, we intend to reduce attacks that could harm our business and jeopardize important data being used, that is the question we understand when and why to work with caution is important in DBA's branch.



Web developer and passioned for web design, SEO and front end technologies.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login