0

I have a function I am creating for some iterative task I need to do but I cannot get the geometry coming out of a record data type into the execute properly:

DO
$$
DECLARE
records record;
images record;
BEGIN                 

FOR records IN SELECT opening, shape FROM d.blocks_2013_2018
LOOP
    TRUNCATE d.xx_delete;

    INSERT INTO d.xx_delete (opening_number, image_name)
    SELECT records.opening, a.image_name
    FROM d.image_tiles_with_blocks a
    WHERE ST_Intersects(a.shape, records.shape);

    FOR images IN SELECT * FROM d.xx_delete
    LOOP
        EXECUTE 'INSERT INTO d.cc_analysis (opening_number, t_id, ht, d_huang, d_huang_adj, d_cortini, geom)
                 SELECT '''||images.opening_number||''', b.t_id, b.max, b.d_huang, b.d_huang_adj, b.d_cortini, b.geom
                 FROM p839.'||images.image_name||'_t b 
                 WHERE ST_Intersects(%, b.shape)', records.shape;   
        COMMIT;
    END LOOP;
END LOOP;
END;
$$
;

It is the record.shape at the end of the code that I cannot get into the code properly. Currently, I am getting an error stating that the "query ... returned two columns"

0

I was able to solve this by replacing the % with $1 and placing USING at the end in replacement of the comma:

EXECUTE 'INSERT INTO d.cc_analysis (opening_number, t_id, ht, d_huang, d_huang_adj, d_cortini, geom)
SELECT '''||images.opening_number||''', b.t_id, b.max, b.d_huang, b.d_huang_adj, b.d_cortini, b.geom
FROM p839.'||images.image_name||'_t b 
WHERE ST_Intersects($1, b.shape)' USING records.shape;   

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.