Fabrica a record in PL/PGSQL
От | Eric E |
---|---|
Тема | Fabrica a record in PL/PGSQL |
Дата | |
Msg-id | 428D14E0.9090208@bonbon.net обсуждение исходный текст |
Список | pgsql-general |
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;
В списке pgsql-general по дате отправления: