CODEWITHSUNDEEP
postgresqlfor-looprecord
ctt_it
ctt_it

Reputation: 339

PostgreSQL function generate csv file

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!

Upvotes: 0

Views: 891

Answers (1)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

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;

Upvotes: 2

Related Questions