Re: appending items to record variable

On Fri, May 14, 2010 at 9:12 AM, x y <charliesen(at)googlemail(dot)com> wrote:
> Hi all
>
> In plpgsql, is there a way to append rows to a record variable?
>
> Each time a query like
> SELECT mycolumn INTO myrecordvariable FROM ...
> is executed, myrecordvariable seems to be reseted and previous entries are lost.
>
> What I want to do is collect values throughout several conditionals
> and then (when the collection is finished) continue processing  these
> values - all in a single function.
>
> An array does not suite because searching is to slow and creating a
> temporary table is not what I want either..
> Or are there any other means to collect items into a searchable list of values?

A record variable is scalar by design -- it's only supposed to hold a
single row. Arrays are the way to do what you want as long as the
number of records is relatively small. Temp tables can be searched,
but they are also brute force unless you index the temp table, and
there are other annoying things about them.

Probably you can fix your problem by using an array and re-thinking
how you express that array in later queries:

foos = foo[];

with (f as select unnext(_foos))
select * from f join foo using (foo_id) ...

Also you probably want to avoid stacking the array iteratively. In
fact, avoid iterative processing wherever possible.

merlin