query result based loop syntax in if condition

Question

I am new to PGSQL and trying to start a loop in database function that iterates on the basis of query result as shown below. I am using 8.2 version.

CREATE OR REPLACE FUNCTION demo(text)
RETURNS SETOF activityhistoryview
    LANGUAGE plpgsql STABLE
    AS $_$
DECLARE
tilldate ALIAS for $1;
actrec revpro_500.activity%ROWTYPE;
BEGIN
    IF tilldate != '' THEN
        FOR actrec IN
            SELECT activity.* from revpro_500.activity WHERE activity.householdid = 950
        LOOP
    ELSE
        FOR actrec IN
            SELECT activity.* from revpro_500.activity WHERE activity.householdid = 500
        LOOP    
    END IF;

        BEGIN
        /* rest code goes here */
        END
    END LOOP;
    RETURN;
END;$_$;

After executing above function, I am getting below error.

ERROR: syntax error at or near "ELSE"

What I am missing here?

If you are new to PostgreSQL, then why use version 8.2 which is already 5 years past its EOL? PG 8.2 is older than the iPhone! — Patrick
– Patrick, Commented Jun 15, 2016 at 7:23

Patrick · Accepted Answer · 2016-06-15 07:39:09Z

1

You can not nest loop queries like that. Instead, first evaluate what you want to do with tilldate, then make a single loop query:

CREATE OR REPLACE FUNCTION demo(tilldate text) RETURNS SETOF activityhistoryview
    LANGUAGE plpgsql STABLE AS $_$
DECLARE
    actrec revpro_500.activity%ROWTYPE;
    hhid integer;
BEGIN
    IF tilldate != '' THEN
        hhid = 950;
    ELSE
        hhid = 500;
    END IF;

    FOR actrec IN
        SELECT * from revpro_500.activity WHERE householdid = hhid
    LOOP
        BEGIN -- Do you really need a transaction block? If not, remove BEGIN/END
        -- rest code goes here
        END
    END LOOP;
    RETURN;
END;$_$;

edited Jun 15, 2016 at 7:39

answered Jun 15, 2016 at 7:30

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KMR · Accepted Answer · 2016-06-15 07:29:16Z

Like in most languages you can't have your control structures overlapping, so on the line before the ELSE you open a LOOP, but do not close it before the ELSE, so the ELSE doesn't have an attached IF paired to it.

You can put the IF/ELSE block inside the loop, or outside it, but not overlapping.

Example:

-- Good
LOOP
    -- some computations
    IF tilldate != '' THEN
        EXIT;  -- exit loop
    ELSE
        -- some computations
    END IF;
END LOOP;

-- Good
IF tilldate != '' THEN
    LOOP
        -- some computations
    END LOOP;
ELSE
    LOOP
        -- some computations
    END LOOP;
END IF;    

-- Bad
IF
    LOOP
        -- some computations
ELSE
    -- some computations
END IF;
END LOOP;

Collectives™ on Stack Overflow

query result based loop syntax in if condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related