Issue with nested loops in postgresql

Question

I am wondering if there is something related to variables and nested loops in postgresql that works differently than in other languages.

Example:

CREATE OR REPLACE FUNCTION public.generate_syllables()
  RETURNS integer AS
$BODY$

DECLARE
w RECORD;
s RECORD;
current_syllable integer := 1;
vowel_trigger integer := 0;
syllable_count integer := 1;

BEGIN

FOR w IN SELECT id FROM words LOOP
    FOR s IN SELECT sound, id FROM sounds WHERE id = w.id ORDER BY ordering LOOP
        IF (SELECT sr.vowel FROM sound_reference sr WHERE sr.sound = s.sound) = 1 AND vowel_trigger = 1 THEN
            syllable_count := syllable_count + 1;
            UPDATE sounds SET syllable = syllable_count WHERE id = s.id;
            vowel_trigger := 0;
        ELSIF (SELECT sr.vowel FROM sound_reference sr WHERE sr.sound = s.sound) = 1 THEN
            vowel_trigger := 1;
            UPDATE sounds SET syllable = syllable_count WHERE id = s.id;
        ELSE
            UPDATE sounds SET syllable = syllable_count WHERE id = s.id;
        END IF;
    END LOOP;
    UPDATE words SET syllables = syllable_count WHERE id = w.id;
    syllable_count := 1;
    vowel_trigger := 0;

END LOOP;

RETURN 1;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE

When I run this function as is, the function never enters the first condition in the if statement. I tested this by adding a return statement within that first condition. At first I thought this must be a logic error, but I have gone through it by hand with examples that are generated from my dataset, and it should work as desired. What is even stranger, is when I comment out the line in the outer loop, for vowel_trigger := 0, then it DOES enter the first if statement. Of course then, the logic does not work correctly either, and from that I have gathered that the syllable_count is being set back to 0 BEFORE the nested loop finishes looping, which would also explain why the first condition is never entered, because vowel_trigger is set back to 0 before the loop makes it back up to the first condition.

In other words, it seems to me that my nested loop is not acting like a nested loop, but rather the nested loop extends into the outer loop, before the nested loop restarts. I imagine I must just not understand how to properly create a nested loop, or perhaps they just can't work this way in POSTGRESQL... any advice would be greatly appreciated.

Nick · Accepted Answer · 2017-06-24 01:37:43Z

You haven't provided table structures and - even more important - data. While your function's behavior really depends on data in tables words, sounds, and sound_reference. For example, if sound_reference is empty, vowel_trigger will never be 1, so the first IF becomes non-achievable.

This will help to debug your function:

RAISE NOTICE 'printlining helps to debug! vowel_trigger=%, syllable_count=%',
  vowel_trigger, syllable_count;

As a side note, I've noticed that UPDATE sounds SET syllable = syllable_count WHERE id = s.id; is repeated in all if/else cases, so it may be worth to move it outside them and place right before the inner END LOOP;.

Addition:

...when I comment out the line in the outer loop, for vowel_trigger := 0, then it DOES enter the first if statement.

It tells us that one of the inner loop's executions ends with vowel_trigger being 1, and it would allow the first IF to trigger, but right outside the inner loop you turn it 0, so the first IF doesn't work then.

Collectives™ on Stack Overflow

Issue with nested loops in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related