Postgres Pro
Downloads
Home   >   mailing lists

Thread: pl/pgsql loop thru columns names

pl/pgsql loop thru columns names

From
Dilyan Berkovski
Date:
Hi All,

I have a nasty table with many repeating columns of the kind port_ts_{i}_<something>, where {i} is from 0 to 31, and
<something>could be 3 different words. 
I have made a pl/pgsql function that checks those columns from port_ts_1_status to port_ts_31_status and counts
something,however this is nasty and ugly thing to do - repeat 31 times one statement for looping thru {i} and do it 3
timesto loop thru <something>. This is how I do it now: 
REATE OR REPLACE FUNCTION auto_util()
  RETURNS "trigger" AS
$BODY$DECLARE
count_free      integer;
util            real;
BEGIN
count_free = 0;
IF new.port_ts_1_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_2_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_3_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_4_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_5_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_6_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_7_status='free' THEN count_free = count_free + 1;
End if;
.... and till the end ilke this.

Can I do a loop, and make the column naming to be build dynamically (as the dynamic queries in pl/pgsql)?
something like
for i in 1..31 do
Loop
If new.port_ts_[i]_status='free' ... and so on
end Loop.
I tryed but unsuccessfully :(, and can not find much in the documentation.
I am using 8.1 and 8.2 Postgresql DBs.
Someone with an idea?
Thanks

Re: pl/pgsql loop thru columns names

From
Pavel Stehule
Date:
2009/8/19 Dilyan Berkovski <berkovski@yahoo.com>:
> Hi All,
>
> I have a nasty table with many repeating columns of the kind port_ts_{i}_<something>, where {i} is from 0 to 31, and
<something>could be 3 different words. 
> I have made a pl/pgsql function that checks those columns from port_ts_1_status to port_ts_31_status and counts
something,however this is nasty and ugly thing to do - repeat 31 times one statement for looping thru {i} and do it 3
timesto loop thru <something>. This is how I do it now: 
> REATE OR REPLACE FUNCTION auto_util()
>  RETURNS "trigger" AS
> $BODY$DECLARE
> count_free      integer;
> util            real;
> BEGIN
> count_free = 0;
> IF new.port_ts_1_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_2_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_3_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_4_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_5_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_6_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_7_status='free' THEN count_free = count_free + 1;
> End if;
> .... and till the end ilke this.
>
> Can I do a loop, and make the column naming to be build dynamically (as the dynamic queries in pl/pgsql)?
> something like
> for i in 1..31 do
> Loop
> If new.port_ts_[i]_status='free' ... and so on
> end Loop.
> I tryed but unsuccessfully :(, and can not find much in the documentation.
> I am using 8.1 and 8.2 Postgresql DBs.
> Someone with an idea?

yes - on this versions, you have to use dynamic pl languages as plperl
or pltcl. On 8.4 you can do it in plpgsql too.

http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html

regards
Pavel Stehule

> Thanks
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: pl/pgsql loop thru columns names

From
Scott Bailey
Date:
Dilyan Berkovski wrote:
> Hi All,
>
> I have a nasty table with many repeating columns of the kind port_ts_{i}_<something>, where {i} is from 0 to 31, and
<something>could be 3 different words. 
> I have made a pl/pgsql function that checks those columns from port_ts_1_status to port_ts_31_status and counts
something,however this is nasty and ugly thing to do - repeat 31 times one statement for looping thru {i} and do it 3
timesto loop thru <something>. This is how I do it now: 
> REATE OR REPLACE FUNCTION auto_util()
>   RETURNS "trigger" AS
> $BODY$DECLARE
> count_free      integer;
> util            real;
> BEGIN
> count_free = 0;
> IF new.port_ts_1_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_2_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_3_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_4_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_5_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_6_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_7_status='free' THEN count_free = count_free + 1;
> End if;
> .... and till the end ilke this.
>
> Can I do a loop, and make the column naming to be build dynamically (as the dynamic queries in pl/pgsql)?
> something like
> for i in 1..31 do
> Loop
> If new.port_ts_[i]_status='free' ... and so on
> end Loop.
> I tryed but unsuccessfully :(, and can not find much in the documentation.
> I am using 8.1 and 8.2 Postgresql DBs.
> Someone with an idea?
> Thanks

Probably normalizing the table would give you the best bang for your
buck if that is an option.