Usage of database stored procedures

Featured Image

Hello guys. Today I’ll try to explain 2 things. First WHY to use stored procedures. And second, HOW to use them. First of all, a small notice. I’ll explain the subject on PostgreSQL example, because of the fact that I used that language syntax most recently.

So let’s begin with the question “Why?“.

Depending on the client’s needs and his limitations sometimes you need to use them to decrease the number of queries to a minimum. An example of that is if you have a situation of network overhead, stored procedures come in handy to minimize the network traffic. Or on the other hand, sometimes you just need to move some of the database logic to a DB side. A good example of that would be if you have a “Cron job” that will clean (truncate) some of the database’s tables.

My example would be based on that specific case, because it will cover some of the most used features and capabilities of PL/pgSQL (SQL procedural language).

So, to begin with next, and more important topic: How to use the database stored procedures?

Let me first give you an example situation scenario. We have a data_table table that contains IDs of fields that we’ll delete, along with other (in this case irrelevant) data. We also have IDs that we need to delete from our database.

Stored procedure would go something like this:

CREATE OR REPLACE FUNCTION delete_data(IN DATA INTEGER[]) RETURNS INTEGER AS
--DECLARATION OF FUNCTION
$$
DECLARE
--DECLARATION OF LOCAL VARIABLES
SQL VARCHAR;
i INTEGER;
BEGIN
--START OF THE PROCEDURE
i := 0;
loop
--LOOP THROUGH AOUR ARRAY OF DATA
IF (DATA[i][1] IS NULL) THEN
RETURN 1;
--IF WE LOOPED THROUG ALL OF ARRAY, IT'S DONE
exit;
END IF;
SQL := 'DELETE FROM data_table WHERE data_table.id='||DATA[i];
--THE SQL ITSELF, COMBINED WITH VARIABLE
EXECUTE(SQL);
--THE EXECUTE FUNCTION BUILT IN PL/pgSQL, EXECUTES VARCHAR SQLs
i := i + 1;
END loop;
--END OF THE PROCEDURE
 
END;
--END OF FUNCTION'S LOGIC
 
$$
LANGUAGE 'plpgsql';
--DECLARATION OF LANGUAGE USED

As you can see, in PostgreSQL, the syntax is a combination of a few others, but regardles of that, now you can just execute a command from your application that would look something like this:

SELECT delete_data(Array[1,5,158,6,77,43]);

To conclude, you may not see the point at this small example, but imagine if you had 30 small changes like this in your application logic at the same time / file. If that was the case here, you would reduce the number of queries to 30 (depending on stored procedures count), from lets say 30*N queries for your N records that you’d use.

Since we are Magento development team, it would make sense if I tell you here that stored procedures can help a lot with maintaining Magentos database. 😀

Well, I tried to explain how and why you should use stored procedures, and hope I managed to do that.


3 comments

  1. How would this example be applied within a Magento Module? i.e. if I wanted to create a function within the Magento Database for my module to use later what would the correct syntax be? I have tried using a version of this but I keep getting an error at the “IN” portion of the create function string.

  2. Hi, when I wrote this, I was looking into how to explain dynamic SQL on extremely simple example.

    It’s not the task I tried to explain, but the stored procedures, and its capabilities.

    And I must agree… “FOR i IN array_lower(data,1) .. array_upper(data,1)” makes much more sense, I simply overlooked that fact…. 😀

  3. Hello

    your example is very terribly written. Why do you use a dynamic sql. It is much slower

    just write:

    DELETE FROM data_table WHERE data_table.id = data[i];

    next mistake: iteration over array

    next mistake: your return value is just one – or raise exception when input array is empty (this is probably bug). In this case is much better use a VOID as return type. So my variant is

    CREATE OR REPLACE FUNCTION delete_data(IN data integer[]) 
    RETURNS void AS $$
    BEGIN
      FOR i IN array_lower(data,1) .. array_upper(data,1)
      LOOP
        DELETE FROM data_table WHERE data_table.id = data[i];
      END LOOP;
    END;
    $$ LANGUAGE plpgsql;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.