[postgis-users] Execute format select into

[Shaozhong SHI]

Hi,  Felix,

Thank you very much.

I worked out  CREATE TABLE AS now.  It is rather confusing that syntax can
vary  between psql, pgadmin and Do loop.

I am doing a rush job to support combating Coronavirus.

Regards,

Shao

On Thu, 26 Mar 2020 at 19:42, Felix Kunde <felix-kunde at gmx.de> wrote:

> Hi,
>
> more of a PostgreSQL question, but anyway.
>
> 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.
> To dynamically create tables you have to use CREATE TABLE AS instead (I
> think). Note, when using format better use %I for tables.
>
> But why create new tables anyway? Write a proper function (instead of
> using DO) and return a set of records.
> Can recommend the PostgreSQL Server Programming book, if you want to dive
> deeper into PL/pgSQL coding.
>
> lg
> Felix
>
>
> *Gesendet:* Donnerstag, 26. März 2020 um 14:51 Uhr
> *Von:* "Shaozhong SHI" <shishaozhong at gmail.com>
> *An:* "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
> *Betreff:* [postgis-users] Execute format select into
> Dear All,
>
> I am trying to use do loop to batch process some data.  See the code below:
>
> Do $$
>
> DECLARE
> schema_name text := 'os_postcodes';
> table_name text := 'code_point_britain_point';
> tbo text :='os_postcodes.code_point_britain_point';
> tb text :='code_point';
> overlay_polygons text := 'admin_boundaries.jsna_warwickshire_polygon';
> ---max = select count(*) from overlay_polygons;
> Counter integer :=0;
>
> BEGIN
> For counter IN 1..3 LOOP
>
> 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);
>
> counter :=counter + 1;
> RAISE NOTICE 'Counter: %', counter;
> END LOOP;
> END; $$
>
>
> My question is how to loop "select into a_table from" to create a series
> of new tables.
>
> Can anyone help?
>
> Regards,
>
> Shao
> _______________________________________________ postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200326/5143d992/attachment.html>