PostgreSQL function generate csv file

Question

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!

provide full sql please

Vao Tsun
– Vao Tsun

2017-03-17 10:57:43 +00:00
Commented Mar 17, 2017 at 10:57 — Vao Tsun
– Vao Tsun, Commented Mar 17, 2017 at 10:57

LongBeard_Boldy · Accepted Answer · 2017-03-17 11:02:59Z

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;

Collectives™ on Stack Overflow

PostgreSQL function generate csv file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related