解决方案
loops 这样的程序元素不是SQL语言的一部分,只能在主体内部使用。 程序语言功能或者 DO 声明,其中这些附加元素由相应的过程语言定义。默认是 PL / pgSQL ,但还有其他。
plpgsql示例:
DO $ $ BEGIN FOR i IN 1..25 LOOP INSERT INTO playtime.meta_random_sample(col_i,col_id) - 使用列名 SELECT i,id FROM tbl ORDER BY random() LIMIT 15000; END LOOP; END $ do $;如果您需要优化效果,请考虑以下相关问题: p>
- 选择随机行PostgreSQL的最佳方式
I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the tool I have. This is what I have so far:
for i in 1..25 LOOP insert into playtime.meta_random_sample select i, ID from tbl order by random() limit 15000 end loop解决方案
Procedural elements like loops are not part of the SQL language and can only be used inside the body of a procedural language function or a DO statement, where such additional elements are defined by the respective procedural language. The default is PL/pgSQL, but there are others.
Example with plpgsql:
DO $do$ BEGIN FOR i IN 1..25 LOOP INSERT INTO playtime.meta_random_sample (col_i, col_id) -- use col names SELECT i, id FROM tbl ORDER BY random() LIMIT 15000; END LOOP; END $do$;If you need to optimize performance, consider this related question:
- Best way to select random rows PostgreSQL
更多推荐
Postgres FOR LOOP
发布评论