<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div>
<div> </div>
<div>more of a PostgreSQL question, but anyway.</div>
<div> </div>
<div>Within the PL/pgSQL language SELECT INTO behaves differently. It does not work as CREATE TABLE AS, but can be used to store query results into variables.</div>
<div>To dynamically create tables you have to use CREATE TABLE AS instead (I think). Note, when using format better use %I for tables.</div>
<div> </div>
<div>But why create new tables anyway? Write a proper function (instead of using DO) and return a set of records.</div>
<div>Can recommend the PostgreSQL Server Programming book, if you want to dive deeper into PL/pgSQL coding.</div>
<div> </div>
<div>lg</div>
<div>Felix</div>
<div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Donnerstag, 26. März 2020 um 14:51 Uhr<br/>
<b>Von:</b> "Shaozhong SHI" <shishaozhong@gmail.com><br/>
<b>An:</b> "PostGIS Users Discussion" <postgis-users@lists.osgeo.org><br/>
<b>Betreff:</b> [postgis-users] Execute format select into</div>
<div name="quoted-content">
<div>Dear All,
<div> </div>
<div>I am trying to use do loop to batch process some data. See the code below:</div>
<div> </div>
Do $$<br/>
<br/>
DECLARE<br/>
schema_name text := 'os_postcodes';<br/>
table_name text := 'code_point_britain_point';<br/>
tbo text :='os_postcodes.code_point_britain_point';<br/>
tb text :='code_point';<br/>
overlay_polygons text := 'admin_boundaries.jsna_warwickshire_polygon';<br/>
---max = select count(*) from overlay_polygons;<br/>
Counter integer :=0;<br/>
<br/>
BEGIN<br/>
For counter IN 1..3 LOOP<br/>
<br/>
execute format('select a.* into %s from %s a where st_intersects(a.geom, (select b.geom from %s b where mi_prinx = %s))=true;', tb, tbo, overlay_polygons,counter, tb);<br/>
<br/>
counter :=counter + 1;<br/>
RAISE NOTICE 'Counter: %', counter;<br/>
END LOOP;<br/>
END; $$
<div> </div>
<div> </div>
<div>My question is how to loop "select into a_table from" to create a series of new tables.</div>
<div> </div>
<div>Can anyone help?</div>
<div> </div>
<div>Regards,</div>
<div> </div>
<div>Shao</div>
</div>
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</div>
</div>
</div></div></body></html>