How to repeat a query in postgreSQL, and add the results to a table

Question

I need to run a query 1000 times, and add the results to a table. I have the following code which is what I would like to be repeated:

Select max_gridco, count(max_gridco) as TaxLots, sum(population) as pop_sum
from
(
    Select tlid, max_gridco, population
    from (
        select tlid, max_gridco, population, st_intersects(tle.geom, acres025.geom)
        from tle, acres025
        where max_gridco not in (0, 1)
        order by RANDOM()
        limit 1000
    ) as count
    where st_intersects = 't'
    order by max_gridco
) as gridcode_count
group by max_gridco;

Is there a way that I can run this 1000 times automatically, and in the output table have a column that includes the run number? So my table would look like the following:

Run number | max_gridco | TaxLots | pop_sum

I am trying to do a Loop command in PgAdmin3, but cannot seem to get the syntax correct.

So you need to insert 1000 values to a table by using a select, right? — Edilson Borges
– Edilson Borges, Commented Jun 8, 2017 at 0:52
No. I'm sorry my first post was confusing. I was having trouble putting it into words. I edited the post to make my question clearer. — Paul Wild
– Paul Wild, Commented Jun 8, 2017 at 1:34

Vao Tsun · Accepted Answer · 2017-06-08 08:02:54Z

2

use DO block or create a function, eg:

DO
$$
begin
for i in 1..1000 loop
    insert into save_to_table (Run number,max_gridco,TaxLots,pop_sum)
    Select i, max_gridco, count(max_gridco) as TaxLots, sum(population) as pop_sum
    from
    (
        Select tlid, max_gridco, population
        from (
            select tlid, max_gridco, population, st_intersects(tle.geom, acres025.geom)
            from tle, acres025
            where max_gridco not in (0, 1)
            order by RANDOM()
            limit 1000
        ) as count
        where st_intersects = 't'
        order by max_gridco
    ) as gridcode_count
    group by max_gridco;
end loop;
end;
$$
;

answered Jun 8, 2017 at 8:02

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Paul Wild Over a year ago

Thank you very much. That is exactly what I was looking for.

Edilson Borges · Accepted Answer · 2017-06-08 00:57:40Z

0

If you need to insert values from a specific select you can do it by using:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

(https://www.w3schools.com/sql/sql_insert_into_select.asp)

Is that what you're looking for?

answered Jun 8, 2017 at 0:57

Edilson Borges

5874 silver badges13 bronze badges

1 Comment

Paul Wild Over a year ago

No. Sorry, I guess I should have been more clear. The query that I have above selects 1000 random rows from a table that I have and summarizes the outputs. It outputs six rows: an identifying number, a count of taxlots, and a sum of population. I am looking to automate the results - repeating the query 1000 times, and listing the output table per run.

Collectives™ on Stack Overflow

How to repeat a query in postgreSQL, and add the results to a table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related