[PostgreSQL]Creating Loop Functions

Create functions in PostgreSQL, which are very useful for various features when working with a large amount of data.
Function Structure in PostgreSQL

CREATE FUNCTION FUNCTION_NAME (param1, param2) RETURNS TYPE_RETURN AS $$
$$ LANGUAGE LANGUAGE_USED;
BODY OF THE FUNCTION

• We have the basic structure of a function, let’s go to the details:
  FUNCTION_NAME: As the name already suggests here you should put the name of your function, without accents, spaces or special characters.
• Param1, param2: Your function can receive parameters of various types and names, here we show only two, but you can use more than two parameters or none.
• TYPE_RETURN: You can return a value (integer, varchar, boolean, etc) or return nothing (void).
• $$: This character at the beginning and at the end of the function is used to tell where it starts and ends respectively.
  PostgreSQL supports several types of languages for you to develop your functions, in that we use plpgsql, but know that you can develop even in “C”.

Nothing better than practical examples to learn how it works. First let’s create a table that will serve as an example. Note the code:
Table_person

CREATE TABLE person(
      id_person SERIAL,
      name VARCHAR(80),
      surname VARCHAR(200),
      sex CHAR(1),
      PRIMARY KEY(id_person)
  );

Before continuing it is important that there is some data in the table, then enter some and continue.
Let’s start with a simple function that just returns the data from the person table.

CREATE FUNCTION get_people() RETURNS SETOF person AS $$
BEGIN
RETURN QUERY SELECT * FROM person
RETURN;
END;
$$ LANGUAGE 'plpgsql'

The function name above is “get_people ()” which returns an SETOF (a list of records) of type person. We could also use RECORD.

What is the difference between the return of the person type and the RECORD? The return of the person type, as the name itself suggests, returns records of the table of physical_body and does not give us the option to return only some fields or even to make a JOIN with other tables returning fields of both, for this we use the RECORD that allows you to return only a few columns of your interest.

Let’s now see how to use FOR LOOP in functions in PostgreSQL.

CREATE OR REPLACE FUNCTION myScheme.cellphone()
    RETURNS SETOF myScheme.telephone AS
  $BODY$
     DECLARE
        --cursor
        reg myScheme.telephone%ROWTYPE;
  BEGIN
           --Loop on all table phones
        FOR reg in
                   SELECT phone.number
                   FROM myScheme.telephone phone
           LOOP
              RETURN NEXT reg;
        END LOOP;
        RETURN;
     END;
     $BODY$
    LANGUAGE plpgsql VOLATILE;

Let’s point out some important points in the above listing:

1. % Rowtype: The rowtype keyword joins the% symbol, says that the variable reg will store a record of type “myScheme.telephone”, that is, a line of the telephone table in the schema “myScheme.”. We could also use RECORD, replacing the whole statement with RECORD only, but we do not need it at the moment.
2. FOR: We perform a SELECT that returns the number, from the telephone table in the myScheme . At each iteration in the FOR loop, the record is stored in the variable reg that can be manipulated within our function. We could, for example, call the field number with: “reg.number”.
3. LOOP and END LOOP: These are the loop delimiters, which tell you where the LOOP starts and ends.
4. $ BODY $: This delimits the beginning and end of the “body” function, ie where logic is actually implemented.

We can also choose to create functions that do not return anything, that is, void:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;

In the above function we have an example of updating the users tables with no return, that is, when executing the function nothing will be returned, only executed internally. Notice that we have a variable called “curtime” that receives the current date and time the function is executed as the default value. As function parameter we have the id and the comment that will be updated for the users table.

You can use RAISE NOTICE when you want to show messages (debugging) while running the function, in cases where the function will take a long time to finish RAISE NOTICE can help show the progress of the function, for example.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
RAISE NOTICE 'updating the record id = %',id;
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;

Note also that as we show new features we learn important ones. The set IF THEN and END IF delimit a conditional block, that is, it will only enter this block if the condition proposed by IF THEN is true. Taking advantage of what we are talking about a conditional block, let’s see how it would look if we wanted to add the ELSE.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $
      DECLARE
          curtime timestamp := now();
      BEGIN
         IF id = 3 THEN
           RAISE EXCEPTION 'Ops, the user of number 3 cannot be    update';
         ELSE
                UPDATE users SET last_modified = curtime, comment = comment
            WHERE users.id = id;
         END IF;   
      END;
 $$ LANGUAGE plpgsql;