I'm writing a function in order to create multiple CSV file.
DECLARE r RECORD;
DECLARE ra RECORD;
DECLARE ro RECORD;
BEGIN
DROP TABLE IF EXISTS tab1;
DROP TABLE IF EXISTS tab2;
DROP TABLE IF EXISTS tab3;
EXECUTE 'CREATE TABLE tab1 AS SELECT DISTINCT num FROM analysis';
FOR r IN EXECUTE 'SELECT num AS num FROM tab1'
LOOP
EXECUTE 'CREATE TABLE tab2 AS SELECT id FROM analysis WHERE num_vista = r.num';
FOR ra IN SELECT id FROM tab2
LOOP
EXECUTE 'CREATE TABLE tab3 AS SELECT DISTINCT oid FROM area WHERE analysis_id = ra.id';
FOR ro IN SELECT oid FROM tab3
LOOP
EXECUTE 'COPY
(
SELECT created_at, result, updated_at FROM area
WHERE oid = ro.oid
) TO "C:/Program Files/PostgreSQL/9.5/data/Num$rOid$ro.csv" WITH CSV HEADER;';
END LOOP;
END LOOP;
END LOOP;
END;
But I have this error: element FROM for table r is missing
LINE 1: ...tab2 AS SELECT id FROM analysiy WHERE num_vista = r.num
Do you know how can I do? Thanks in advance!
execute "text" query statement cant access values outside the query expression , you must generate those query's addicting values as text ... .
E r RECORD;
DECLARE ra RECORD;
DECLARE ro RECORD;
BEGIN
DROP TABLE IF EXISTS tab1;
DROP TABLE IF EXISTS tab2;
DROP TABLE IF EXISTS tab3;
EXECUTE 'CREATE TABLE tab1 AS SELECT DISTINCT num FROM analysis';
FOR r IN EXECUTE 'SELECT num AS num FROM tab1'
LOOP
EXECUTE 'CREATE TABLE tab2 AS SELECT id FROM analysis WHERE num_vista = '||r.num::text;
FOR ra IN SELECT id FROM tab2
LOOP
EXECUTE 'CREATE TABLE tab3 AS SELECT DISTINCT oid FROM area WHERE analysis_id = '||ra.id::text;
FOR ro IN SELECT oid FROM tab3
LOOP
EXECUTE 'COPY
(
SELECT created_at, result, updated_at FROM area
WHERE oid = '||ro.oid::text||'
) TO "C:/Program Files/PostgreSQL/9.5/data/Num$rOid$ro.csv" WITH CSV HEADER;';
END LOOP;
END LOOP;
END LOOP;
END;