Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7A47E650074 for ; Sat, 2 Aug 2008 06:39:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80713-05 for ; Sat, 2 Aug 2008 06:39:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by postgresql.org (Postfix) with ESMTP id 57F54650115 for ; Sat, 2 Aug 2008 06:39:52 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so986886fgg.23 for ; Sat, 02 Aug 2008 02:39:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=XVhBMyKT/9226uE1CFG3+EWp9Fetzi67UoRdJrLvoF8=; b=PGVE80tgL5AJunVU71PFrVI85zYRnlCfP6Z1XCEGq2F6FIYGEab3b2wq4UNVxb53AH BYlZ91sitoNFezBZjnNdsTcVgxIeYCofe/O8OOYEZdyl9YRBD4TyE9MasrEyJOg7FJLC ZLeajneDL8u38p5vvkZrIvhdlDnCbHAylp+cA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=JanPnCd06mogxQup3woH/O61Ck5z9sJDTyOEQBwDbpRCRZlwoOx05IKrWPcnzxALLL YzPmqHErhlYAwaf13vYAsOzinIn4yPyHAJtLjlRj/JWvT8o+0/r0OHcFIsnwz2qCAalJ 08/H2yRvblGUPIZQ/5z5phHGgzX9mQ1J7um/c= Received: by 10.86.79.19 with SMTP id c19mr1456141fgb.79.1217669990766; Sat, 02 Aug 2008 02:39:50 -0700 (PDT) Received: by 10.86.92.18 with HTTP; Sat, 2 Aug 2008 02:39:50 -0700 (PDT) Message-ID: Date: Sat, 2 Aug 2008 15:09:50 +0530 From: "C K" To: "pgsql-admin@postgresql.org" Subject: functions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13034_24722685.1217669990749" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/4 X-Sequence-Number: 29849 ------=_Part_13034_24722685.1217669990749 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Dear Friends, I have written a function as follows. Here I need to get the sum of balance before the user provided date. The below function works well, but returns records as a set of values, rather I need individual columns. What to do? CREATE OR REPLACE FUNCTION aa(date) RETURNS SETOF record AS $BODY$SELECT docacctransactions.accgroupid, Sum(docacctransactions.credit) AS OSC, Sum(docacctransactions.debit) AS OSD FROM docs INNER JOIN docacctransactions ON docs.docid = docacctransactions.docid WHERE docs.includeinbalcalc=-1 AND docs.transactiontype<>1 AND docs.docdate<=$1 GROUP BY docacctransactions.accgroupid; $BODY$ LANGUAGE 'sql' VOLATILE COST 100 ROWS 1000; ALTER FUNCTION aa(date) OWNER TO sa; I am using WinXP SP 2 with PostgreSQL 8.3 with ODBC Please help. CPK -- Keep your Environment clean and green. ------=_Part_13034_24722685.1217669990749 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Dear Friends,
I have written a function as follows. Here I need to get the sum of balance before the user provided date. The below function works well, but returns records as a set of values, rather I need individual columns. What to do?
CREATE OR REPLACE FUNCTION aa(date)
  RETURNS SETOF record AS
$BODY$SELECT docacctransactions.accgroupid, Sum(docacctransactions.credit) AS OSC, Sum(docacctransactions.debit) AS OSD
FROM docs INNER JOIN docacctransactions ON docs.docid = docacctransactions.docid
WHERE docs.includeinbalcalc=-1 AND docs.transactiontype<>1 AND docs.docdate<=$1
GROUP BY docacctransactions.accgroupid;
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION aa(date) OWNER TO sa;

I am using WinXP SP 2 with PostgreSQL 8.3 with ODBC
Please help.
CPK
--
Keep your Environment clean and green.
------=_Part_13034_24722685.1217669990749--