X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 280C3D1B4E5 for ; Thu, 23 Oct 2003 21:00:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 35705-06 for ; Thu, 23 Oct 2003 18:00:10 -0300 (ADT) Received: from 127.0.0.1 (unknown [195.66.192.168]) by svr1.postgresql.org (Postfix) with SMTP id D3374D1B50E for ; Thu, 23 Oct 2003 18:00:03 -0300 (ADT) Received: (qmail 14845 invoked by uid 0); 23 Oct 2003 21:00:01 -0000 Received: from unknown (HELO shadow) (1.1.1.2) by 0 with SMTP; 23 Oct 2003 21:00:01 -0000 Content-Type: text/plain; charset="us-ascii" From: Alexander Vlasenko Reply-To: intrnl_edu@ilyichevsk.odessa.ua Organization: ND Center To: pgsql-general@postgresql.org Subject: extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax Date: Fri, 24 Oct 2003 00:00:00 +0300 X-Mailer: KMail [version 1.4] MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310240000.00608.intrnl_edu@ilyichevsk.odessa.ua> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BAYES_30, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200310/1191 X-Sequence-Number: 51264 Please CC me, I am not subscribed. An imaginary SQL statement INSERT INTO table FETCH ... FROM cursor; looks almost the same as currently available INSERT INTO table SELECT ...; I tried it because I needed to insert a row in a table after I DELETEd a set of rows, something like this: BEGIN; DECLARE total CURSOR FOR SELECT=20 SUBSTR(datetime,1,7)||'-01 00:00:00' as month, client, SUM(money) FROM stat WHERE SUBSTR(datetime,1,7)=3D'2003-10' GROUP BY month,client; DELETE FROM stat WHERE SUBSTR(datetime,1,7)=3D'2003-10'; INSERT INTO stat FETCH ALL FROM total; COMMIT; but it does not work, chokes on FETCH ALL. I want to sum up all the money by month, delete all the rows (possibly thousands of them) and insert one row per client with monthly totals. Obviously I cannot swap order of INSERT and DELETE here. I hesitate to post this to pgsql-hackers@postgresql.org, do I have to? ;) --=20 Alexander Vlasenko