In its actual code returns an error in line 20
here the code corrected a probed:
CREATE OR REPLACE FUNCTION
create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_param ALIAS FOR $1;
pivot_column ALIAS FOR $2;
select_column ALIAS FOR $3;
pivot_table ALIAS FOR $4;
aggregate_func ALIAS FOR $5;
aggr_column ALIAS FOR $6;
pivot_record RECORD;
create_view TEXT;
BEGINSELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT '' || quote_ident(select_column) ;
FOR pivot_record IN
EXECUTE ''SELECT DISTINCT CAST(''
|| quote_ident(pivot_column)
|| '' AS TEXT) AS col1 FROM ''
|| quote_ident(pivot_table)
|| '' order by 1''
LOOP
create_view :=
create_view || '','' || aggregate_func ||
''(CASE '' || quote_ident(pivot_column) ||
'' WHEN '' || quote_literal(pivot_record.col1) ||
'' THEN '' || quote_ident(aggr_column) ||
'' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
END LOOP;
create_view :=
create_view || '','' || aggregate_func ||
''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
'' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
'' GROUP BY '' || quote_ident(select_column);
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
Вариант для распечатки
Разговоры, обсуждение новостей (Public)
on 23-Ноя-07, 17:48 
