X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 68AEFD1B52D for ; Tue, 21 Oct 2003 18:45:50 +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 26509-06 for ; Tue, 21 Oct 2003 15:45:20 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 88094D1B510 for ; Tue, 21 Oct 2003 15:45:18 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3802398; Tue, 21 Oct 2003 11:45:58 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Wilhelm Graiss , "'pgsql-sql@postgresql.org'" Subject: Re: plpgsql related question: intervals and variables Date: Tue, 21 Oct 2003 11:43:02 -0700 User-Agent: KMail/1.4.3 References: <40925A3BA1EBD711A35C000102B6E57D06C9A7@BAL_PROXY00> In-Reply-To: <40925A3BA1EBD711A35C000102B6E57D06C9A7@BAL_PROXY00> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310211143.02533.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/274 X-Sequence-Number: 15674 Willhelm, > Begin >=20 > heute :=3D ''today''; > Select Into vk ourcolumn From table where other =3D foo; > If vk > 0 Then > vk_txt :=3D ''Vorkuehlung notwendig'';=20=20=20=20=20=20=20=20 > ez :=3D heute + interval ''vk days'';=09 PL/pgSQL handles variable like SQL, not like PHP or Perl. You can't do a= =20 variable substitution inside quotes, and you need to cast: ez :=3D heute + interval (cast(vk as text) || '' days''); Also, the string 'today' has no special meaning in PL/pgSQL. I think you w= ant=20 now() instead. I'm afraid that you're going to need a tutorial on SQL datatypes, casting, = and=20 similar issues ... I wish I had one to recommend to you. Just keep in mind= =20 that SQL scripting languages (like PL/pgSQL) are not Perl! --=20 -Josh Berkus Aglio Database Solutions San Francisco