Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f32HKIK17656 for ; Mon, 2 Apr 2001 13:20:18 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 32642 for ; Mon, 02 Apr 2001 10:20:13 -0700 From: "Josh Berkus" Subject: Fwd: Calling functions inside a function: behavior To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 02 Apr 2001 10:20:13 -0700 Message-ID: MIME-Version: 1.0 Content-Type: message/rfc822 X-Archive-Number: 200104/14 X-Sequence-Number: 2353 From: "Josh Berkus" Subject: Re: [SQL] Calling functions inside a function: behavior To: Edipo Elder Fernandes de Melo X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 02 Apr 2001 08:22:48 -0700 Message-ID: In-Reply-To: <200104021150.f32BoqK60742@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Edipo, > FUNCTION (...) AS '(...)BEGIN > PERFORM FUNCTION1(); > PERFORM FUNCTION2(); > (...) > END;' LANGUAGE 'PLPGSQL'; I'm not sure about that syntax (PERFORM). I ususally set my functions equal to a value ('remote_result := Function1(paramater)'). This has the added advantage of letting me use an exit value from my sensted function to communicate with the parent function ('IF remote_result = 'ERROR' THEN ... '). I'm just not sure what bugaboos you may be running into with PERFORM. One possibility (Tom, Jan, verify me on this): All calls in a function are automatically nested in a transaction. Thus, if FunctionM calls Function1, 2, and 3, then the system should reverse Functions 1, 2, and 3 if M errors out at any point. This means that all of the changes made by the nested calls need to be cached somehow; on a slow or low-memory system, this could lead to bogging down as your machine utilizes its swap space if Functions 1, 2, and 3 involve heavy data interactions. One way to test this, is to modify your test script as follows: BEGIN WORK; PERFORM Function1; SELECT current_timestamp; PERFORM Function2; SELECT current_timestamp; etc ... COMMIT WORK; If the test script bogs down as well, you have your answer although the workaround may be tricky to implement. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco