CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Nunners
Nunners

Reputation: 17

Iterating through select result in PostgreSQL without PL/pgSQL

I am currently working on a system built to handle the different reservations of classes and cubicles in a school building. I have it such a way that when you are reserving a classroom you are automatically reserving every cubicle inside of it.

I need to iterate through the result of a select in pure SQL without the use of PL/pgSQL to achieve this reservation as it's not exactly clear how many cubicles a classroom has prior to querying it.

I've already tried PL/pgSQL, and while it does work well, I would need an approach that doesn't use PL/pgSQL for class requirements. Here's a sample of my PL/pgSQL implementation with the FOR keyword. 

CREATE OR REPLACE FUNCTION createReservationClass(p_codeBuilding CHAR(2),p_codeClass INT,p_CIP VARCHAR(8),p_date DATE,p_startPeriod INT,p_endPeriod INT,p_description VARCHAR(1024))
RETURNS VOID
AS $$
    FOR temprow IN SELECT CODE_CUBICULE  FROM public.CUBICULE where CODE_BUILDING = p_codeBuilding AND CODE_CLASS = p_codeClass
    LOOP
        SELECT createReservationCubicule(p_codeBuilding, p_codeClass, temprow.CODE_CUBICULE, p_CIP, p_date, p_startPeriod, p_endPeriod, p_description);
    END LOOP;
END;
$$
LANGUAGE PLPGSQL;

I'd like it that when I am given a building number and a classroom number, it will automatically reserve every cubicle it also has. I'm not sure if I have the right approach of if there is an easier way to go about it.

The only requirement is I am not allowed to use PL/pgSQL to write my function.

Upvotes: 0

Views: 104

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246598

Why the weird requirement not to use PL/pgSQL? However, you can easily do it with an SQL function.

Stop thinking procedurally and start thinking in SQL. What you want to do can be done with a single query without (explicit) loops:

SELECT createReservationCubicule(
          p_codeBuilding,
          p_codeClass,
          CODE_CUBICULE,
          p_CIP,
          p_date,
          p_startPeriod
          p_endPeriod,
          p_description
       )
FROM public.CUBICULE
where CODE_BUILDING = p_codeBuilding
  AND CODE_CLASS = p_codeClass;

Upvotes: 1

Related Questions