While learning and translating VBA functions to PLpgSQL (PL), I have run up against a problem that needs a solution rather quickly. I am concerned that PL may have a limitation that could prevent me from standardizing a particular function. Here it goes...
I need to be able to work with an in memory representation of a custom type and manipulate the data over a loop. Sounds simple right? Here is an oversimplified version of what I need to accomplish...
CREATE TYPE mytype AS (assetid text, assetamt numeric(15,2));
CREATE OR REPLACE FUNCTION __assets() RETURNS SETOF mytype AS
$$
DECLARE
assets mytype%rowtype;
BEGIN
FOR assets IN SELECT * FROM myassets;
LOOP
RETURN NEXT assets;
END LOOP;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION process() RETURNS numeric(15,2) AS $$
DECLARE
cur refcursor;
currentrec record;
BEGIN
OPEN cur FROM SELECT * FROM __assets();
FETCH ALL FROM cur; -- Doesn't work
FETCH ALL IN cur; -- Doesn't work
-- WHAT DOES WORK?
FETCH cur INTO currentrec;
currentrec.assetamt = currentrec.assetamt * .12;
RETURN currentrec.assetamt;
END
$$ LANGUAGE plpgsql;
Does anyone see my dilemna? Let me list them...
1. How in the heck to I get FETCH to work in my favor because obviously, PL does not seem nearly as comprehensive as Oracle's PL.
2. According to the documentation, I can only use cursor's in a read only fashion. Guys, this defeats the purpose of creating the darn custom data type and in-memory reference, but I don't know of any other way to access the data set!!! In Oracle's PL, I can use the SENSITIVE/INSENSITIVE option for read/write attributes, but this is not allowed in PL.
What I am trying to accomplish is to manipulate a collection of records defined by a custom data type and update them depending on payments received. In other words, process the collection based on a record set of payments made on the assets.
Can any one make recommendations or enlighten me? If I need to use another language, should I use plPerl, or plPHP instead? Any intelligent recommendations, links, especially examples, on this topic would be very helpful.
DISCLAIMER: Code above was at the top of my head and NOT tested. You may need to edit code to make it work properly.
Using PostgreSQL 8.0 on Windows 2003 Server. (no jabs please!, I intend on porting to FreeBSD or Linux later.)
Thanks in advance,
Gary
gwinn7
I need to be able to work with an in memory representation of a custom type and manipulate the data over a loop. Sounds simple right? Here is an oversimplified version of what I need to accomplish...
CREATE TYPE mytype AS (assetid text, assetamt numeric(15,2));
CREATE OR REPLACE FUNCTION __assets() RETURNS SETOF mytype AS
$$
DECLARE
assets mytype%rowtype;
BEGIN
FOR assets IN SELECT * FROM myassets;
LOOP
RETURN NEXT assets;
END LOOP;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION process() RETURNS numeric(15,2) AS $$
DECLARE
cur refcursor;
currentrec record;
BEGIN
OPEN cur FROM SELECT * FROM __assets();
FETCH ALL FROM cur; -- Doesn't work
FETCH ALL IN cur; -- Doesn't work
-- WHAT DOES WORK?
FETCH cur INTO currentrec;
currentrec.assetamt = currentrec.assetamt * .12;
RETURN currentrec.assetamt;
END
$$ LANGUAGE plpgsql;
Does anyone see my dilemna? Let me list them...
1. How in the heck to I get FETCH to work in my favor because obviously, PL does not seem nearly as comprehensive as Oracle's PL.
2. According to the documentation, I can only use cursor's in a read only fashion. Guys, this defeats the purpose of creating the darn custom data type and in-memory reference, but I don't know of any other way to access the data set!!! In Oracle's PL, I can use the SENSITIVE/INSENSITIVE option for read/write attributes, but this is not allowed in PL.
What I am trying to accomplish is to manipulate a collection of records defined by a custom data type and update them depending on payments received. In other words, process the collection based on a record set of payments made on the assets.
Can any one make recommendations or enlighten me? If I need to use another language, should I use plPerl, or plPHP instead? Any intelligent recommendations, links, especially examples, on this topic would be very helpful.
DISCLAIMER: Code above was at the top of my head and NOT tested. You may need to edit code to make it work properly.
Using PostgreSQL 8.0 on Windows 2003 Server. (no jabs please!, I intend on porting to FreeBSD or Linux later.)
Thanks in advance,
Gary
gwinn7
