Invalid syntax in loop SQL

Question

I have an error in syntax @ in my code in postgreSQL database, but I don't know what could be wrong. Did I implemented the SQL loop code in rigth way? My SQL code:

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 1000)
BEGIN
   INSERT INTO punkty (geog) SELECT ST_GeometryN(st_asText(ST_GeneratePoints(geom,1000)), @Counter) FROM panstwo
   SET @Counter  = @Counter  + 1
END

Lukasz Szozda · Accepted Answer · 2020-06-07 13:28:50Z

2

First your code looks like T-SQL(Microsoft and Sybase dialect) and it won't work on PostgreSQL by design

Second loop is not required:

INSERT INTO punkty (geog)
SELECT ST_GeometryN(st_asText(ST_GeneratePoints(geom,1000)), s.Counter) 
FROM panstwo
CROSS JOIN generate_series(1,1000) s(counter);

answered Jun 7, 2020 at 13:28

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

preemek088 Over a year ago

thank you! But the function is generating 1000 random elements in every time when counter encreaseS. Do you know how to generate 1000 points once and then INSERT every point (1000 points) using this loop?

Collectives™ on Stack Overflow

Invalid syntax in loop SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related