Thread: How to return a modified array from a user-define function...

Hi:

I would like to create a standard deviation function using the basic
SQL statements "CREATE AGGREGATE" and "CREATE FUNCTION" using Postgres 7.0.2.

I can't find a way to return a modified array.

I've tried things like:

CREATE function x (_float8, float8) RETURNS _float8 AS
'SELECT {$1[1] + 1, $1[2] + $2, $1[3] + ($2 * $2)};'
LANGUAGE 'sql';

This doesn't work. I get the following error:

ERROR:  parser: parse error at or near "{"

Any help will be appreciated!
--
D. Jay Newman                   ! For the pleasure and the profit it derives
jay@sprucegrove.com              ! I arrange things, like furniture, and
http://www.sprucegrove.com/~jay/   ! daffodils, and ...lives.  -- Hello Dolly

"D. Jay Newman" <jay@sprucegrove.com> writes:
> I would like to create a standard deviation function using the basic
> SQL statements "CREATE AGGREGATE" and "CREATE FUNCTION" using Postgres 7.0.2.

> I can't find a way to return a modified array.

It's possible to do this in pltcl but I don't think there is any syntax
for constructing array values on-the-fly in SQL or plpgsql functions.
(A definite shortcoming, that; should have a TODO item for it.)

BTW, stddev/variance are in current sources for 7.1.

            regards, tom lane