Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is there a RECORD[] type in plpgsql?

Is there a RECORD[] type in plpgsql?

From
Nick Boutelier
Date:
Im using the same...

FOR record_or_row IN query LOOP

at the beginning of a function and at the end of a function. Is there
a way to save the query results in a RECORD[] type so that I don't
have to run the query twice?

Re: Is there a RECORD[] type in plpgsql?

From
Sam Mason
Date:
On Fri, Jul 24, 2009 at 11:42:07PM -0700, Nick Boutelier wrote:
> Im using the same...
>
> FOR record_or_row IN query LOOP
>
> at the beginning of a function and at the end of a function. Is there
> a way to save the query results in a RECORD[] type so that I don't
> have to run the query twice?

Wouldn't that be a temporary table?

--
  Sam  http://samason.me.uk/

Re: Is there a RECORD[] type in plpgsql?

From
Merlin Moncure
Date:
On Sat, Jul 25, 2009 at 2:42 AM, Nick Boutelier<namethisapp@gmail.com> wrote:
> Im using the same...
>
> FOR record_or_row IN query LOOP
>
> at the beginning of a function and at the end of a function. Is there
> a way to save the query results in a RECORD[] type so that I don't
> have to run the query twice?

yes (in postgresql 8.3).  This is often a little more convenient than
a temporary table.

merlin

Re: Is there a RECORD[] type in plpgsql?

From
Nick Boutelier
Date:
Thanks Merlin, do you know what the syntax would be? Can't seem to
find it anywhere and im getting an error using 8.3.7

Re: Is there a RECORD[] type in plpgsql?

From
Merlin Moncure
Date:
On Sun, Jul 26, 2009 at 3:30 PM, Nick Boutelier<namethisapp@gmail.com> wrote:
> Thanks Merlin, do you know what the syntax would be? Can't seem to
> find it anywhere and im getting an error using 8.3.7

You will need to create a type to represent the record contained in
the array, or use a table type (each table can also be used from a
composite type).

Inside your plpgsql function,

DECLARE
  foos foo[];
BEGIN
  select array(select * from foo) into foos;
...
:-)

merlin