如何在Postgres底下設定PL/SQL 陣列資料型態的功能

How To PL/SQL in Postgres With Array Data Type

如何在postgres底下設定PL/SQL 陣列資料型態的功能
* postgres pl install and array avg function setup

http://frakkle.com/archives/archive_2006-m04.php

幫postgre開啟PL/SQL的功能
joec@celica:~$ createlang plpgsql arraydata1

在postgres底下設定PL/SQL 陣列資料型態
arraydata1=#

CREATE OR REPLACE FUNCTION array_to_set(ar_in numeric[])
RETURNS SETOF numeric AS
$BODY$
declare
rtn numeric;
ar_pointer int4 = 1;
BEGIN
--ar_upper := array_upper( ar_in );
WHILE ( ar_in[ar_pointer] IS NOT NULL ) LOOP
return next ar_in[ar_pointer];
ar_pointer := ar_pointer + 1;
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql' STRICT IMMUTABLE;

postgres底下設定PL/SQL 陣列資料型態的平均，總合，計算次數，標準偏差數的功能

* create sum, count, avg, standard deviation functions
arraydata1=#
CREATE OR REPLACE FUNCTION sum(numeric[])
RETURNS numeric AS
$BODY$
SELECT sum( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE FUNCTION count_array(numeric[])
RETURNS bigint AS
$BODY$
SELECT count( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION avg(numeric[])
RETURNS numeric AS
$BODY$
SELECT avg( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION stddev(numeric[])
RETURNS numeric AS
$BODY$
SELECT stddev( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

arraydata1=# select avg(array_num[32:34]) from data;