Hello Folks!

In this post, I’ll explain you how to write a postgresql function that iterates over a set of rows. Sometimes there is a need in my projects where I have to loop over some rows in a table. Select certain columns and take decision on them.

Let’s begin with it. I will strongly recommend you to play with sample tables and data. Don’t try this directly on your production database :).

Create two tables

  • employee (id INTEGER, name TEXT, department TEXT)
  • reward (id serial PRIMARY KEY, employee_id INTEGER REFERENCES employee(id))
CREATE OR REPLACE FUNCTION loop_over_rows() RETURNS BOOLEAN AS $$
DECLARE rec RECORD;
BEGIN

    FOR rec IN (SELECT id, name, department FROM employee) LOOP
        RAISE INFO '%', rec;
        INSERT INTO reward VALUES(DEFAULT, rec.id) WHERE rec.name = 'viju';
        RAISE INFO 'DONE';
    END LOOP;
    RETURN TRUE;
END;
$$ LANGUAGE PLPGSQL;

Above function iterates over records of employee table. It inserts employee_id in reward table where the name of employee is viju. So, you can see that based on value of one table, we are updating another table.

I have kept this very simple to explain in easy way. On the basis of this, you can design your function to do more complex tasks.

I will leave you try this out. Any suggestion, question is mot welcome.

Have a great day!


Aryan

Hey Friends, Thank you for visiting my blog and spending some time reading it. Myself Aryan who works in computer technologies and keeps on pen down my understanding of various topics. This blog is a write-up of my understanding of various technologies after reading different materials about it. I hope that it helps you in some way.

0 Comments

Leave a Reply

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