Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 10BA8476324 for ; Mon, 13 May 2002 22:28:47 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 6D8B34759F6 for ; Mon, 13 May 2002 22:11:27 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4E2BJW22078; Mon, 13 May 2002 22:11:19 -0400 (EDT) To: Marc SCHAEFER Cc: pgsql-sql@postgresql.org Subject: Re: Various PostgreSQL questions In-reply-to: References: Comments: In-reply-to Marc SCHAEFER message dated "Mon, 13 May 2002 18:33:37 +0200" Date: Mon, 13 May 2002 22:11:18 -0400 Message-ID: <22075.1021342278@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200205/127 X-Sequence-Number: 7763 Marc SCHAEFER writes: > Version: 7.1release-3.potato.1 (Debian package of 7.1release-3) > Question 1: > Apparently using NUMERIC(10, 2) in a type definition (DECLARE) of > a stored procedure in PL/pgSQL doesn't work; FLOAT works instead. I believe this is fixed in 7.2. > Question 2: > How can I implement a constraint which would always ensure the > SUM(money_amount) WHERE type = 1 in a specified table is always > zero ? I would think you'd want your function to run AFTER INSERT not BEFORE INSERT. > The following works, once. The second time it doesn't work (in the > same session/backend, see below for the error). If you want to build dynamically-modified queries in plpgsql, you need to use EXECUTE. That includes references to tables that you're dropping and recreating between calls of the function. regards, tom lane