CODEWITHSUNDEEP
postgresql
hsnsd
hsnsd

Reputation: 1823

Loop over results from a query to use in another query

I have a query which returns a specific data set

select user_id, county from users

Primary key is on user_id, county

Lets say this returns the following:

1   "HARRIS NORTH"
1   "HANOVER"
3   "MARICOPA"
4   "ADAMS"
5   "CUMBERLAND"

Next, I want to run a different set of query for all the records I obtain from the above query.

COPY( WITH myconstants (_id, _county) as (
   values (1, 'HARRIS NORTH')
)
 SELECT d.* FROM data d, myconstants
where user_id = _id and county = _county) 
TO '/tmp/filename.csv' (format CSV);

How can I do this in a loop for all the records from my 1st query using postgres only?

A psuedocode of what I want to achieve:

for (a_id, a_county) in (select user_id, county from users):
    COPY( WITH myconstants (_id, _county) as (
        values (a_id, a_county))
    SELECT d.* FROM data d, myconstants
        where user_id = _id and county = _county) 
        TO '/tmp/filename.csv' (format CSV);

Upvotes: 0

Views: 185

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246568

There is no need to loop in SQL:

COPY (SELECT d.*
      FROM data d
         JOIN users
            ON d.user_id = users.user_id AND d.country = users.country) 
TO '/tmp/filename.csv' (format CSV);

Upvotes: 1

Related Questions