Thread: functions
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.
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.
Dear Friends,
--
Keep your Environment clean and green.
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.
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.
--
Keep your Environment clean and green.
create a type and return this --- On Sat, 8/2/08, C K <shreeseva.it@gmail.com> wrote: > From: C K <shreeseva.it@gmail.com> > Subject: [ADMIN] functions > To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> > Date: Saturday, August 2, 2008, 9:39 AM > 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.