Substitute value within a query in postgresql

Question

Substitute value within a query in postgresql

Navigation

#1 by (1 votes)

1

Good day. I have the following query:

COPY (SELECT 
"UR_16PIX_Chih"."ID_UR", 
"UR_16PIX_Chih"."IDEDOMUN15", 
"UR_16PIX_Chih"."PIX1", 
"UR_16PIX_Chih"."PIX2", 
"UR_16PIX_Chih"."PIX3", 
"UR_16PIX_Chih"."PIX4", 
"UR_16PIX_Chih"."PIX5", 
"UR_16PIX_Chih"."PIX6", 
"UR_16PIX_Chih"."PIX7", 
"UR_16PIX_Chih"."PIX8", 
"UR_16PIX_Chih"."PIX9", 
"UR_16PIX_Chih"."PIX10", 
"UR_16PIX_Chih"."PIX11", 
"UR_16PIX_Chih"."PIX12", 
"UR_16PIX_Chih"."PIX13", 
"UR_16PIX_Chih"."PIX14", 
"UR_16PIX_Chih"."PIX15", 
"UR_16PIX_Chih"."PIX16" 
 FROM 
 public."UR_16PIX_Chih"
 WHERE 
 "UR_16PIX_Chih"."IDEDOMUN15" = '08061') TO 
 '/home/manager/data/miconsulta/08061.csv' WITH CSV HEADER;

What I want to know is how I can automatically replace the value '08061' with the value 08062, then with the value 08063, then with the value 08062, and so on. Only in the query, not in the table. So far what I do is edit the query and change the value manually but I need to do it automatically. Thanks in advance for the help.

sql postgresql

asked by Jc Cabrera 17.04.2017 в 06:25

source

1 answer

Display a message with an array Problem loading URL

score 1 · Answer 1

Basically you have a DO block that iterates over all the different values of a field in your table and creates a csv file for each one.

You can do something like:

DO 
$_$
DECLARE 
    r record;
BEGIN 
FOR r IN SELECT "IDEDOMUN15"  as i FROM "UR_16PIX_Chih" GROUP BY "IDEDOMUN15" LOOP
    COPY (
        SELECT 
            "ID_UR", 
            ... -- acá todos tus campos
            "PIX16" 
         FROM 
            public."UR_16PIX_Chih"
         WHERE 
            "IDEDOMUN15" = r.i) TO '/home/manager/data/miconsulta/'||r.i||'.csv' WITH CSV HEADER;
END LOOP;
END;
$_$
language plpgsql;