CODEWITHSUNDEEP
postgresqlrepeat
Paul Wild
Paul Wild

Reputation: 135

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

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.

Upvotes: 0

Views: 2909

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

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;
$$
;

Upvotes: 2

Edilson Borges
Edilson Borges

Reputation: 587

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?

Upvotes: 0

Related Questions