funcion TRansformstr2

Encontré esto en el sitio, les escribo para saber si alguno de ustedes puede
darme información de cuáles son los parámetros que debe recibir:

_____

* From: "Jose Ayala - INCALPACA TPX" <jayala ( at ) incalpaca ( dot )
com <mailto:jayala(at)DOMAIN(dot)HIDDEN> >
* To: "Edwin Quijada" <listas_quijada ( at ) hotmail (
<mailto:listas_quijada(at)DOMAIN(dot)HIDDEN> dot ) com>
* Subject: TRANSFORM plpgsql
* Date: Mon, 28 Mar 2005 09:17:50 -0500

_____

size=2 width="100%" align=center>

CREATE OR REPLACE FUNCTION "public"."transformstr2" (text, text, text, text,
text, text) RETURNS text AS'

declare
rec record; --record type for et returning
pivot_col alias for $1; -- What column must we pivot on?
qry_data alias for $2; -- query that contains data ..
headers alias for $3; -- headers to use ..

hd_col_name alias for $4; -- column of original table that contain the
headers of the new table ..

sum_val alias for $5; -- what to sum up
nullval alias for $6; -- what to use as null value ..
inner_sql text; -- internal sql query
outer_sql text; -- external sql query
headers_len int; -- length of the headers sting

headers_cnt int; -- this is the counter we use while going through the
headers csv string

test int; -- only for testing purposes..

headers_separator text; --what separator to use for the csv of the headers
(default: ";" (semicolon)) cur_separator text; -- This is the current
position in the headers csv string last_separator int; -- this is to
remember where the last separator was (the last ;) col_val text; -- this is
where we store the value of the column that has to become the header of the
new table. sql_string text; -- this is the whole sql string that will
produce the result of the crosstab query

begin
headers_cnt := 1;
test := 0;
headers_separator := '';''; -- this is the separator of the pivot columns ..
cur_separator := 0; -- initialize
last_separator := 0; -- initialize
inner_sql := ''''; -- initialize
outer_sql := ''''; --initialize
sql_string := ''''; --initialize
-- ---------------- BUILD SQL STRINGS ----------------------------
-- Split the csv names of cols ..
-- Length of string
headers_len := length(headers);
while headers_cnt <= headers_len loop
-- if the next character is ; then proceed
if substr(headers, headers_cnt, 1) = headers_separator then
-- we have a ";" so lets start
-- build the first part of inner_sql (the case whens ..)
-- store the name of the header col ..

col_val := substr(headers,last_separator + 1, headers_cnt -1 -
last_separator);

-- inner_sql := inner_sql || col_val || '' '';

inner_sql := inner_sql || ''case when '' || hd_col_name || ''='' || chr(39)
|| col_val || chr(39) || '' then '';

inner_sql := inner_sql || ''sum('' || sum_val || '') else '';
inner_sql := inner_sql || nullval || '' end as '' || col_val || '','';

-- build the outer_sql .. the second query to actually group everything up

outer_sql := outer_sql || ''sum(''|| col_val || '') as '' || col_val ||
'','';

-- remember position of previous ";"
last_separator := headers_cnt ;
end if;
-- increment the counter ..
headers_cnt := headers_cnt + 1;
end loop;
-- -------------------INNER SQL----------------------------
-- remove the last comma from the end of the cases ..
inner_sql := trim(trailing '','' from inner_sql);
-- now build the actual SQL string
inner_sql := ''SELECT '' || pivot_col || '','' || inner_sql || '' from '';

inner_sql := inner_sql || ''('' || qry_data || '') as foo GROUP BY '' ||
pivot_col || '','' || hd_col_name;

-- -------------------INNER SQL----------------------------
-- -------------------OUTER SQL----------------------------
-- trim the las comma from outer_sql
outer_sql := trim(trailing '','' from outer_sql);
-- build the string
outer_sql := ''SELECT '' || pivot_col || '','' || outer_sql || '' FROM ('';
-- -------------------OUTER SQL----------------------------
-- -----------------FULL SQL STRING----------------------------
sql_string := sql_string || outer_sql || inner_sql;
sql_string := sql_string || '') as bar group by '' || pivot_col;
-- -----------------FULL SQL STRING----------------------------

-- ---------------- BUILD SQL STRINGS ----------------------------
return sql_string;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

ANA