×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Array Manipulation in PLpgSQL?

Array Manipulation in PLpgSQL?

Array Manipulation in PLpgSQL?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close