X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B13F59FA1B0 for ; Tue, 2 May 2006 14:15:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 59261-08 for ; Tue, 2 May 2006 14:15:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.204]) by postgresql.org (Postfix) with ESMTP id 670449FB1D5 for ; Tue, 2 May 2006 14:15:00 -0300 (ADT) Received: by xproxy.gmail.com with SMTP id h31so318362wxd for ; Tue, 02 May 2006 10:14:59 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:subject:date:user-agent:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:x-virus-scanned; b=mD8SshGjA4GthQdUPOMz0U0it4z95T95962rFdGx9C/PZikh2y9I3AtQ+1COJyNl3ANM68wB76EtYt9jkhLFPqctGqHIbGH73kvpm8Fe9MVF6YNpdbtcBF4cTqibEWMGUTaKOFXPDaBQrEbSgY9EtyBZ/23Dpzt+3CJkFk/m0OQ= Received: by 10.70.59.8 with SMTP id h8mr716742wxa; Tue, 02 May 2006 10:14:59 -0700 (PDT) Received: from strongwill.g2ctech ( [201.22.8.126]) by mx.gmail.com with ESMTP id i36sm361529wxd.2006.05.02.10.14.58; Tue, 02 May 2006 10:14:59 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by strongwill.g2ctech (Postfix) with ESMTP id C13D88514F for ; Tue, 2 May 2006 14:14:53 -0300 (BRT) Received: from strongwill.g2ctech ([127.0.0.1]) by localhost (strongwill [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 03917-01-4 for ; Tue, 2 May 2006 14:14:53 -0300 (BRT) Received: from jupiter.g2ctech (unknown [10.0.0.240]) by strongwill.g2ctech (Postfix) with ESMTP id A3632AA28 for ; Tue, 2 May 2006 14:14:53 -0300 (BRT) Received: from localhost (localhost [IPv6:::1]) by jupiter.g2ctech (Postfix) with ESMTP id 37AD4E25A5 for ; Tue, 2 May 2006 14:14:50 -0300 (BRT) From: Jorge Godoy To: pgsql-sql@postgresql.org Subject: Creating nested functions with plpgsql Date: Tue, 2 May 2006 14:14:48 -0300 User-Agent: KMail/1.9.1 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200605021414.49661.jgodoy@gmail.com> X-Virus-Scanned: amavisd-new at g2ctech.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/13 X-Sequence-Number: 25044 Hi! Is it possible to create nested functions using plpgsql as the language? I= 'd=20 like to avoid other dependencies if I can, besides using already written SQ= L=20 code...=20 There are lots of repetitive tasks that "subfunctions" would solve in a very elegant way, keeping code more readable and concise. I was thinking something along the lines of: CREATE FUNCTION outer_function(param1 DATE) RETURNS date AS $$ DECLARE BEGIN =A0 =A0 CREATE FUNCTION inner_function(OUT output_day DATE) AS $_$ =A0 =A0 DECLARE =A0 =A0 =A0 =A0 output_day DATE; =A0 =A0 BEGIN =A0 =A0 =A0 =A0 -- do something to calculate output_day =A0 =A0 END;=20 =A0 =A0 $_$ language plpgsql stable strict;=20 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -- here I could have ot= her languages or=20 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -- even restrict the in= ner function to the =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -- same language as the= outer function... =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -- $_$ is different fro= m $$ intentionally. =A0 =A0 -- do something in main function that uses inner function several =A0 =A0 -- times. END; $$ language plpgsql; =A0 =A0=20 I have some real case examples where this could be useful, if it is needed.= =20 I haven't pasted them here because the smallest one has 176 LOC, after refactoring with nested functions. If it is not possible, are there any plans to allow this kind of thing?=20 (Even with a different syntax it would be good to have it.) TIA, =2D-=20 Jorge Godoy