Skip site navigation (1) Skip section navigation (2)

Re: requesting features in PostgreSQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <list-pgsql-general(at)empires(dot)org>
Cc: Andrew Gould <andrewgould(at)yahoo(dot)com>,Postgres Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: requesting features in PostgreSQL
Date: 2002-04-27 15:39:17
Message-ID: 20898.1019921957@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Jeff Davis <list-pgsql-general(at)empires(dot)org> writes:
> create function a1(float[2],float) returns float[2] as '
> DECLARE
> ret float[2];
> BEGIN
> ret := ''{'' || (($1[1]) * ($2)) || '','' || (($1[2]) + 1) || ''}'';
> RETURN ret;
> END;
> ' language 'plpgsql';

We really need better support for arrays in plpgsql :-(.  The above will
work, but it invokes conversion of floats to text and back again on
every call; that's slow and will probably cause accumulation of roundoff
errors in the aggregate result.

I tried to do this:

create function a1(float[2],float) returns float[2] as '
declare
ret float[2];
begin
ret := $1;
ret[1] := ret[1] * $2;
ret[2] := ret[2] + 1;
return ret;
end' language plpgsql;

but it failed with syntax errors --- plpgsql doesn't understand the
notation "var[subscript] := something".  Someone oughta dig into it
and fix that.

In the meantime I think the most practical way to do this task in
plpgsql is to abuse the built-in "point" type, which can be treated
as an array of 2 floats:

regression=# create function a1(point,float) returns point as '
regression'# begin
regression'# return point($1[0] * $2, $1[1] + 1);
regression'# end' language plpgsql;
CREATE
regression=# select a1('(2,3)'::point, 44);
   a1
--------
 (88,4)
(1 row)

Note that the subscripts are [0],[1] not [1],[2] ... a bit of legacy
incompatibility ...

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Mathieu ArnoldDate: 2002-04-27 16:52:23
Subject: Re: delete column
Previous:From: Tom LaneDate: 2002-04-27 15:14:50
Subject: Re: creating a dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group