[GRASS-user] db.select limitation + psql workaround

[Glynn Clements]

"Peter Löwe" wrote:

> > > db.select balks when the SELECT queries are sufficiently complex
> > (experienced in GRASS6.3).
> > > 
> > > Example:
> > > 
> > > echo 
> > > "SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
> > county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
> > county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
> > BY FOO.county_name_ascii,FOO.county_id;" 
> > >  | db.select
> > > 
> > > results in:
> > > DBMI-Postgres driver error:
> > > Cannot select:
> > > SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
> > county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
> > county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
> > BY FOO.county_name_ascii,FOO.county_id;
> > 
> > Could this be because of the SELECT ... INTO which in my logic is a 
> > special case of the SELECT statement which would need to be executed via 
> > db.execute as db.select only handles "pure" selections, i.e. no 
> > modifications or creations ?

Technically, SELECT INTO is an entirely different command to SELECT,
not a special case of it.

[Similarly, there is no CREATE command, only CREATE TABLE, CREATE
DATABASE, and so on.]

FWIW, the PostgreSQL documentation says that CREATE TABLE AS (which is
a different command to CREATE TABLE) is the recommended method.

> I do agree. However, since the statement begins with the
> "SELECT"-string, db.execute refuses to execute it.

This is a bug in db.execute, and the only simple fix is to remove the
check altogether.

Distinguishing SELECT from SELECT INTO is hard enough if you know the
exact language syntax, but db.execute has to work with all DBMI
drivers. The only realistic way to perform such a check would be to
implement it in the individual drivers.

-- 
Glynn Clements <glynn at gclements.plus.com>