Thread: Returning an array form a function

Hi,

I'm trying to return an array from a postgres function, but I keep
getting weird errors:

-- this works
CREATE OR REPLACE FUNCTION test(float4[])
 RETURNS float4[]
AS '
BEGIN
 RETURN $1;
END;'
LANGUAGE 'plpgsql';

select test('{1,2}');

-- but I want to change the values in the array:
CREATE OR REPLACE FUNCTION test(float4[])
 RETURNS float4[]
AS '
DECLARE
 a_return[] float4;
 -- a_return[2] float4;
BEGIN
 a_return[1] := 1;
 select 2 into a_return[2];
 RETURN a_return;
END;'
LANGUAGE 'plpgsql';



I just can't seem to declare an array.  How should I be doing this?

Ones Self wrote:
> -- but I want to change the values in the array:
> CREATE OR REPLACE FUNCTION test(float4[])
>  RETURNS float4[]
> AS '
> DECLARE
>  a_return[] float4;
>  -- a_return[2] float4;
> BEGIN
>  a_return[1] := 1;
>  select 2 into a_return[2];
>  RETURN a_return;
> END;'
> LANGUAGE 'plpgsql';
>
> I just can't seem to declare an array.  How should I be doing this?
>

I think you *might* be able to do what you want using a combination of
loops, string concatenation, and EXECUTE statements to manually build a
new array string; but at best it would be a real PITA (I'm currently
working to improve this for 7.4 BTW).

Also, one important note is that IIRC, in 7.3.x (and probably before),
function parameters declared as "datatype[]" do not get correctly typed
as an array, they get typed as the element type. Look at your function
using
   \df test
and see what it says your arguments are.

This is almost dead simple however using PL/R (simple, that is, if you
already know how to install and use R ;-)):

CREATE OR REPLACE FUNCTION test(_float4,int,float4)
  RETURNS float4[]
AS '
  arg1[arg2]<-arg3;
  return(arg1)
' LANGUAGE 'plr';

regression=# select test('{1,2,3}'::float4[],2,0);
   test
---------
  {1,0,3}
(1 row)

If you are interested, you can get PL/R here:
   http://www.joeconway.com/

It requires at least Postgres 7.3, and I've not tried it with anything
earlier than R 1.6.2.

It might be easy using PL/Perl, PL/Tcl, and/or PL/Python also, but since
I don't use them very often I'll not try to show how -- maybe someone
else can offer an example.

HTH,

Joe