Can't use LOOP PostgreSQL

Question

I'm facing an issue from yesterday and I can't understand why my SQL is not working.. This may be a simple error since i'm a beginner in SQL but I can't find where it is.

Here is what I try to do:

CREATE FUNCTION test() RETURN integer AS $$
BEGIN
    FOR i IN 1..5 LOOP
    SELECT * from result WHERE id=i;
end loop;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

This is just a simple loop as I can find in the documentation but I have this error:

Error report -
ERROR: syntax error at or near "RETURN" (this is the first RETURN statement in the function)

The database is in PostgreSQL and the version is 9.4.5

Why it's not working ?

This code does not make any sense to me. What exactly are you trying to do there? Why are you running the same query over and over again and then discarding the result? — user330315
– user330315, Commented Feb 14, 2020 at 8:44
Unrelated to your problem, but: Postgres 9.4 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Feb 14, 2020 at 8:45
@a_horse_with_no_name It's just to test the LOOP function, the main objective of my function is to migrate 6 columns from a table to another which is new, and add the foreign key referencing the new table into the old table, but as every statement I use makes errors I just try to made a simple loop and add my process after. — Spialdor
– Spialdor, Commented Feb 14, 2020 at 8:47
I think you have to type RETURNS with the final S. postgresql.org/docs/9.4/sql-createfunction.html — Nicola Lepetit
– Nicola Lepetit, Commented Feb 14, 2020 at 8:54

Laurenz Albe · Accepted Answer · 2020-02-14 08:58:47Z

1

There are several problems, apart from the fact that the function isn't doing anything useful:

It must be RETURNS integer, not RETURN integer.

That't what causes the error.
The SELECT has no destination. Either add an INTO clause or discard the result with
```
PERFORM * from result WHERE id=i;
```
You should indent the code correctly, so that you can read and understand it.

answered Feb 14, 2020 at 8:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Can't use LOOP 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