[postgis-users] Execute format select into

[Regina Obe]

For what it’s worth SELECT * INTO table_to_be_created FROM some_other_table;

 

Is valid SQL construct.  It’s more supported than CREATE TABLE AS  across different database systems.

 

I use SELECT * INTO  syntax all the time in SQL even in PostgreSQL  and it works fine.

 

What you are constructing in your loop is an SQL statement so should be able to use INTO table_to_be_created.  

However I notice your table you are creating is not in the loop so would fail on second run because you keep on trying to create the table.

 

Your code should be something like

 

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 || counter::text, tbo, overlay_polygons,counter, tb);

-- counter :=counter + 1; #you don’t need this the IN clause already does this
RAISE NOTICE 'Counter: %', counter;
END LOOP;
END; $$ 

 

 

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shaozhong SHI
Sent: Thursday, March 26, 2020 7:32 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Execute format select into

 

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 <mailto: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 <mailto:shishaozhong at gmail.com> >
An: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org <mailto: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 <mailto:postgis-users at lists.osgeo.org>  https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto: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/01c52b7c/attachment.html>