Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Christoph Haller <ch@rodos.fzk.de>,
	intrnl_edu@ilyichevsk.odessa.ua
Subject: Re: extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax
Date: Mon, 27 Oct 2003 11:35:58 -0800
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200310271539.QAA10042@rodos>
In-Reply-To: <200310271539.QAA10042@rodos>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200310271135.59460.josh@agliodbs.com>

Alexander,

> > BEGIN;
> > DECLARE total CURSOR
> >  FOR SELECT=3D20
> >   SUBSTR(datetime,1,7)||'-01 00:00:00' as month,
> >   client,
> >   SUM(money)
> >  FROM stat
> >  WHERE SUBSTR(datetime,1,7)=3D3D'2003-10'
> >  GROUP BY month,client;
> > DELETE FROM stat WHERE SUBSTR(datetime,1,7)=3D3D'2003-10';
> > INSERT INTO stat FETCH ALL FROM total;
> > COMMIT;
> >=20
> > but it does not work, chokes on FETCH ALL.

Well, there's two problems with your program:

1) INSERT INTO .... FETCH ALL is not currently implemented.  You would need=
 to=20
use a loop, and insert one row at a time by value.

2) You can't insert the rows you've just deleted from the base tables.  In=
=20
your example, the TOTAL cursor would be empty.   I think that what you real=
ly=20
want is a temp table.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco