Thread: Add items to a record variable

Add items to a record variable

From

eddy sanchez

Date:

02 March 2007, 00:54:05

WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia


Can anyone help me???

I work with plpgsql and I need to add items to a record variable, with a for
statement, something like this:

declare
   v_rec record;
begin
for nn in (some_xpresion) loop
 ....

v_rec = vrec + [new_item]; <--Here I need to add an item to record variable with
                               each loop
end loop lp;

return v_rec;
end;


The result should be like {it1,it2,it3,i4,....}

Please can you help me?
Thanks a lot

-------------------------------------------------
Este email se envio mediante el servidor WEBMAIL de UDABOLnet
-------------------------------------------------

Re: Add items to a record variable

From

"Postgres User"

Date:

02 March 2007, 03:48:05

If you need to return a record to another function or client program,
you can always use this sytnax:

SELECT var_a::integer, var_b::integer, var_c::integer... etc

where var_a, var_b, etc and local variables that hold your calculated values.

You dont need to create a local record structure- select will do that
for you.  The '::integer' will ensure that the values are typecast as
integers.


On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>
> Thank you for you help
>
> The item is a field, I need to return a record with double precision or integer
> fields.
>
> I need a function that can calculate some fields (integer numbers) that should
> be added to a record inside a loop, and the result, must seems like this:
>
> {234.00, 56434.78, 5556.89,....}
>
> Always, I call those functions with:
>
> select * from func_with_record() as (field1 integer, field2 integer, field3
> integer,....)
>
> Do you undestand?
>
> Thanks a lot for your interesting.
>
>
>
> -----------------------------------
>
> What is the new item?  Is it a field?
>
> On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>
>
> Can anyone help me???
>
> I work with plpgsql and I need to add items to a record variable, with a for
> statement, something like this:
>
> declare
>  v_rec record;
> begin
> for nn in (some_xpresion) loop
>  ....
>
> v_rec = vrec + [new_item]; <--Here I need to add an item to record variable with
>                              each loop
> end loop lp;
>
> return v_rec;
> end;
>
>
> The result should be like {it1,it2,it3,i4,....}
>
> Please can you help me?
> Thanks a lot
>
> -------------------------------------------------
> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
> -------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>
> -------------------------------------------------
> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
> -------------------------------------------------
>

Re: Add items to a record variable

From

"Eddy D. Sanchez"

Date:

02 March 2007, 04:15:07

Thanks.

But I need to add fields to a defined and filled record, I don't know
if I can do it, and how can I do it.

If I have:

v_record record;

EXECUTE 'select * from table'
INTO v_record;

supposing (after query) that v_record contains just one row with 5
fields, I need to add the 6th, 7th, 8th fields, etc, etc.

Do you understand ?



On Mar 2, 2007, at 12:48 AM, Postgres User wrote:

> If you need to return a record to another function or client program,
> you can always use this sytnax:
>
> SELECT var_a::integer, var_b::integer, var_c::integer... etc
>
> where var_a, var_b, etc and local variables that hold your
> calculated values.
>
> You dont need to create a local record structure- select will do that
> for you.  The '::integer' will ensure that the values are typecast as
> integers.
>
>
> On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
>> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>>
>> Thank you for you help
>>
>> The item is a field, I need to return a record with double
>> precision or integer
>> fields.
>>
>> I need a function that can calculate some fields (integer numbers)
>> that should
>> be added to a record inside a loop, and the result, must seems
>> like this:
>>
>> {234.00, 56434.78, 5556.89,....}
>>
>> Always, I call those functions with:
>>
>> select * from func_with_record() as (field1 integer, field2
>> integer, field3
>> integer,....)
>>
>> Do you undestand?
>>
>> Thanks a lot for your interesting.
>>
>>
>>
>> -----------------------------------
>>
>> What is the new item?  Is it a field?
>>
>> On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
>> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>>
>>
>> Can anyone help me???
>>
>> I work with plpgsql and I need to add items to a record variable,
>> with a for
>> statement, something like this:
>>
>> declare
>>  v_rec record;
>> begin
>> for nn in (some_xpresion) loop
>>  ....
>>
>> v_rec = vrec + [new_item]; <--Here I need to add an item to record
>> variable with
>>                              each loop
>> end loop lp;
>>
>> return v_rec;
>> end;
>>
>>
>> The result should be like {it1,it2,it3,i4,....}
>>
>> Please can you help me?
>> Thanks a lot
>>
>> -------------------------------------------------
>> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> -------------------------------------------------
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>      subscribe-nomail command to majordomo@postgresql.org so that
>> your
>>      message can get through to the mailing list cleanly
>>
>>
>>
>> -------------------------------------------------
>> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
>> -------------------------------------------------
>>

Re: Add items to a record variable

From

"Postgres User"

Date:

02 March 2007, 05:20:48

The first hack is probably a little easier.  Let me try and flesh it
out for you:

declare
    rec1   record;
    rec2   record;
    new_fields  varchar = '';

begin
   select fieldlist from mytable into rec1;
   -- inspect and play with your rec1 here
   -- now start a loop to add new fields

      if new_fields != '' then
         new_fields = new_fields || ', '
      end if;

       new_fields = new_fields || new field value here || '::' || new
field type here;

   -- close loop here

   -- now select all rows into a new record variable

   execute 'select mt.fieldlist ' || new_fields || ' from mytable mt into rec2';

end;

This is a hack but it should work.  You'll end up with a single record
var with your old fields and new fields, typed as you have specified.


On 3/1/07, Eddy D. Sanchez <eddy.sanchez@gmail.com> wrote:
> Good idea, thanks a lot, I try it
>
> On Mar 2, 2007, at 1:32 AM, Postgres User wrote:
>
> > Ok, you can always SELECT into a temp table.  If you want to add
> > additional fields in a loop, you can call an Execute 'Alter Table Add
> > Column....'
> > to add all the fields you need.  Then call Update to insert values
> > into the fields.
> >
> > Finally do a select into your record var.
> >
> > On 3/1/07, Eddy D. Sanchez <eddy.sanchez@gmail.com> wrote:
> >> Thanks.
> >>
> >> But I need to add fields to a defined and filled record, I don't know
> >> if I can do it, and how can I do it.
> >>
> >> If I have:
> >>
> >> v_record record;
> >>
> >> EXECUTE 'select * from table'
> >> INTO v_record;
> >>
> >> supposing (after query) that v_record contains just one row with 5
> >> fields, I need to add the 6th, 7th, 8th fields, etc, etc.
> >>
> >> Do you understand ?
> >>
> >>
> >>
> >> On Mar 2, 2007, at 12:48 AM, Postgres User wrote:
> >>
> >> > If you need to return a record to another function or client
> >> program,
> >> > you can always use this sytnax:
> >> >
> >> > SELECT var_a::integer, var_b::integer, var_c::integer... etc
> >> >
> >> > where var_a, var_b, etc and local variables that hold your
> >> > calculated values.
> >> >
> >> > You dont need to create a local record structure- select will do
> >> that
> >> > for you.  The '::integer' will ensure that the values are
> >> typecast as
> >> > integers.
> >> >
> >> >
> >> > On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
> >> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
> >> >>
> >> >> Thank you for you help
> >> >>
> >> >> The item is a field, I need to return a record with double
> >> >> precision or integer
> >> >> fields.
> >> >>
> >> >> I need a function that can calculate some fields (integer numbers)
> >> >> that should
> >> >> be added to a record inside a loop, and the result, must seems
> >> >> like this:
> >> >>
> >> >> {234.00, 56434.78, 5556.89,....}
> >> >>
> >> >> Always, I call those functions with:
> >> >>
> >> >> select * from func_with_record() as (field1 integer, field2
> >> >> integer, field3
> >> >> integer,....)
> >> >>
> >> >> Do you undestand?
> >> >>
> >> >> Thanks a lot for your interesting.
> >> >>
> >> >>
> >> >>
> >> >> -----------------------------------
> >> >>
> >> >> What is the new item?  Is it a field?
> >> >>
> >> >> On 3/1/07, eddy sanchez <eddysan@udabol.edu.bo> wrote:
> >> >> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
> >> >>
> >> >>
> >> >> Can anyone help me???
> >> >>
> >> >> I work with plpgsql and I need to add items to a record variable,
> >> >> with a for
> >> >> statement, something like this:
> >> >>
> >> >> declare
> >> >>  v_rec record;
> >> >> begin
> >> >> for nn in (some_xpresion) loop
> >> >>  ....
> >> >>
> >> >> v_rec = vrec + [new_item]; <--Here I need to add an item to record
> >> >> variable with
> >> >>                              each loop
> >> >> end loop lp;
> >> >>
> >> >> return v_rec;
> >> >> end;
> >> >>
> >> >>
> >> >> The result should be like {it1,it2,it3,i4,....}
> >> >>
> >> >> Please can you help me?
> >> >> Thanks a lot
> >> >>
> >> >> -------------------------------------------------
> >> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
> >> >> -------------------------------------------------
> >> >>
> >> >> ---------------------------(end of
> >> >> broadcast)---------------------------
> >> >> TIP 1: if posting/reading through Usenet, please send an
> >> appropriate
> >> >>      subscribe-nomail command to majordomo@postgresql.org so that
> >> >> your
> >> >>      message can get through to the mailing list cleanly
> >> >>
> >> >>
> >> >>
> >> >> -------------------------------------------------
> >> >> Este email se envio mediante el servidor WEBMAIL de UDABOLnet
> >> >> -------------------------------------------------
> >> >>
> >>
> >>
>
>

Re: Add items to a record variable

From

Martijn van Oosterhout

Date:

02 March 2007, 15:35:57

On Thu, Mar 01, 2007 at 08:54:38PM -0400, eddy sanchez wrote:
> WEBMAIL Server: UDABOLnet, Universidad de Aquino Bolivia
>
>
> Can anyone help me???
>
> I work with plpgsql and I need to add items to a record variable, with a for
> statement, something like this:

<snip>

Does it have to be a record variable? An array seems far more
appropriate.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

signature.asc