[GENERAL] Fabrica a record in PL/PGSQL

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[GENERAL] Fabrica a record in PL/PGSQL

Eric E
Hi all,
    I'm trying to write a function that takes the following records

|    Field1    |    Field2   |    Field3     |    Field 4
|      A         |       P       |    Name1   |      51
|      A         |       P       |    Name2   |      20.143
|      A         |       P       |    Name3   |      32.7
|      A         |       P       |    Name4   |      5.22
|      A         |       P       |    Name5   |      14.34
|      A         |       Q       |    Name2   |      1.111
|      A         |       Q       |    Name7   |      9.712
|      A         |       Q       |    Name3   |      2.33
|      A         |       Q       |    Name1   |      77
|      B         |       P        |    Name1   |      75
|      B         |       P        |    Name4   |      2.66
|      B         |       P        |    Name5   |      2.63

And turn it into:
|   Field1   |   Field 2   |  1st              |        2nd            
|    3rd                       |           4th           |          
5th           |
|      A      |       P        |  Name1: 51  |  Name3: 32.7    |      
Name2: 20.143  |  Name5: 14.34   |   Name4: 5.22   |
|      A      |       Q       |  Name1: 77  |   Name7: 9.712 |    
Name3:  2.33      |    Name2: 1.111 |          Null          |
|      B      |        P       |  Name1: 75  |   Name4: 2.66   |    
Name5:  2.63      |         Null            |          Null          |

etc.

Sort of like a crosstab query, but populating the columns in order of
the value in Field 4 and then concatenating with Field3

My approach to writing this function would be as below, but I can't
figure out how to do the bread and butter of the function: assign the
values into fields according to their order.  Any help or ideas would be
greatly appreciated.

Many thanks,

Eric

CREATE FUNCTION sorta-crosstab RETURNS setof RECORD AS
DECLARE
crFields CURSOR FOR SELECT Field1,Field2,Field3,Field4  FROM table1
ORDER BY field1 ASC, field2 ASC, field4 DESC;
rwFields table1%ROWTYPE;
rcResults RECORD;

BEGIN
OPEN crFields
LOOP  -- over rows
    FETCH crFields INTO rwFields;
    EXIT WHEN NOT FOUND
    IF ... THEN - Loop over equal values of Field1 and Field2
   -- Add to rcResults field for "1st","2nd", "3rd", etc.     \  These
are what I don't know how to do!
   --  Assign value to field "1st","2nd","3rd",etc.              /
    ELSE
    RETURN NEXT;
    END;
END LOOP;
RETURN;
END;




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly