Thread: accumulate setof ?

accumulate setof ?

From
Bo Lorentsen
Date:
Hi ...

I have tried to make a plgpsql function that accumulate a set of integers, but I find no way of doing this in the PostgreSQL documentation. I have made a very simple example to demonstrate my problem :

CREATE FUNCTION fn_integer_list( INTEGER )
RETURNS SETOF INTEGER AS '
DECLARE
  SETOF INTEGER res;
BEGIN
  res := 1;
  res := res + $1;
  RETURN res;
END'
LANGUAGE 'plpgsql';

There is really two problems here. How does one initialize a set of integers, and how does one append a new value to the setof integers ? This example does, of course, not work :-)

If there is a reference to some documentation I should read, I will be happy for any hint in its direction.

/BL

Re: accumulate setof ?

From
Jason Tan
Date:

I ahvd a play with this a bit last night.

I cant even work out how to declare an array in plpgsql.

I did a search of the docs on teh psogtres site for the word "setof" and
the hits I got back indicated that it si intended to be used when a
_query_ executed in a plpgsql fucntion returns result.

And that was the only example I could find.

I couldnt find any examples of arrays beign sued in pgsql either.

Not helpful,but maye setof isnt designed to be used how you are trying to
use it.(ie maybe there si no operator for concatenationg  data to a
setof).

I am goignto play with this a ibit more when I get time, because pl/pgsql
interests me, but it probalby wont be for a while.

Jason

On Tue, 11 Sep
2001, Bo Lorentsen wrote:

> Hi ...
>
> I have tried to make a plgpsql function that accumulate a set of
> integers, but I find no way of doing this in the PostgreSQL
> documentation. I have made a very simple example to demonstrate my
> problem :
>
> CREATE FUNCTION fn_integer_list( INTEGER )
> RETURNS SETOF INTEGER AS '
> DECLARE
>   SETOF INTEGER res;
> BEGIN
>   res := 1;
>   res := res + $1;
>   RETURN res;
> END'
> LANGUAGE 'plpgsql';
>
> There is really two problems here. How does one initialize a set of
> integers, and how does one append a new value to the setof integers ?
> This example does, of course, not work :-)
>
> If there is a reference to some documentation I should read, I will be
> happy for any hint in its direction.
>
> /BL
>

--
------------------------------------------------------------------------------
Jason Tan                                                jason@rebel.net.au
     "Democracy is two wolves and a lamb voting on what to have for lunch.
                 Liberty is a well-armed lamb contesting the vote."
                               ~Benjamin Franklin, 1759
------------------------------------------------------------------------------


Re: accumulate setof ?

From
Bo Lorentsen
Date:
Jason Tan wrote:

> Not helpful,but maye setof isnt designed to be used how you are trying to
> use it.(ie maybe there si no operator for concatenationg  data to a
> setof).

This is exactly my problem. I know how to declare and select values, and return a
set of integers, but I'm not able to append values myself from a plpgsql script.

I have tried to look into the function list "\df", but nothing really seems to do
what I need, and I have also tried more naive atempts like : "ilst := ilst + 1"
or even "ilst := [1,2,3]", but with no luck :-)

> I am goignto play with this a ibit more when I get time, because pl/pgsql
> interests me, but it probalby wont be for a while.

Sad, I do like to hear from you again if you ever get to this, as it would make
my life much more easy (and my database faster :-))

/BL