Postgresql function If condition then run query else stop

Question

I need some help with the following postgres functionality

I have the following table with the columns:

array, array_length

I initially have a few arrays in and then I run a query(actually a set of 3 queries) that selects the arrays, appends them, and then inserts the appended arrays into the table.

I need to loop this insert query until one of the arrays (select max(array_length) from table) reaches a predefined size, say length 50.

I basically need to write something like

begin 
for (select max(array_length) from table)<50
loop 
(drop table if exists super_item_temp;
ALTER TABLE super_item
    RENAME TO super_item_temp;

create table super_item as 
select distinct * from super_item_temp;

insert into super_item 
select ...calculations... from super_item)
end

but I cannot find the right syntax to write this

I looked at the manual at http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html but I could not find anything helpful.

Any hints or links pointing me in the right direction would be appreciated! Thank you.

Edit: I tried

CREATE OR REPLACE FUNCTIOn function_name() RETURNS int4 AS ' DECLARE r RECORD;

BEGIN WHILE (select max(array_length) from super_item)<50

loop
drop table if exists super_item_temp;
    ALTER TABLE super_item
        RENAME TO super_item_temp;

    create table super_item as 
    select distinct * from super_item_temp;


    insert into super_item 
    select old_array, 
    array_sort_unique( array_agg(added_item) || a.old_array) as new_array, 
    array_length(array_sort_unique( array_agg(added_item) || a.old_array),1)

    from (
            select 
    a.new_array as old_array,

    case when string_to_array(b.item2::text, ",")::int[] <@ a.new_array and string_to_array(b.item1::text, ",")::int[] <@ a.new_array then null
     when string_to_array(b.item2::text, ",")::int[] <@ a.new_array then b.item1 else b.item2 end  as added_item

    from

    super_item a 
    left join pairs b 

    on  string_to_array(b.item1::text, ",")::int[] <@ a.new_array or  string_to_array(b.item2::text, ",")::int[] <@ a.new_array /**any item from pair is in array**/
    where 1=1

    group by a.new_array, 2

    having sum(b.count)>10
    and sum(b.offset)<=0 
    and 
    case 
    when string_to_array(b.item2::text, ",")::int[] <@ a.new_array and string_to_array(b.item1::text, ",")::int[] <@ a.new_array then null
    when string_to_array(b.item2::text, ",")::int[] <@ a.new_array then b.item1 else b.item2 end is not null
    /**new item is not null**/
    order by 2 desc
    )a
    group by 1;
END LOOP;

return 1; END; ' LANGUAGE plpgsql;

SELECT function_name() as output;

now I am getting an error 'unknown column' for a function argument which should be a quoted separator.

error: column „,“ existiert nicht LINE 18: on string_to_array(b.item1::text, ",")::int[] <@ a.new_ar...

use while instead of for --> WHILE (select max(array_length) from table)<50 LOOP ..... — krokodilko
i tried, i am just not sure what to put for arguments and so on. I don't care about the arguments or what it returns, i just need it to run the loop when called. I added to the post. — AdrianBR
Please post the table definition - CREATE TABLE super_item .... statement. — krokodilko
CREATE TABLE super_item ( old_array integer[], new_array integer[], array_length integer ) WITH ( OIDS=FALSE ); — AdrianBR
There is no 'END LOOP' statement in the code - the right syntax is WHILE condition LOOP statements END LOOP. — krokodilko

krokodilko krokodilko · Accepted Answer · 2013-10-24T09:58:59

Try this, on my PC it compiles fine (however I don't know whether it works as expected).

CREATE OR REPLACE FUNCTION append_super_item ()
RETURNS integer AS $length$
declare
    length integer;
BEGIN
  WHILE (select max(array_length) from super_item)<50
    LOOP
       drop table if exists super_item_temp;
       ALTER TABLE super_item
            RENAME TO super_item_temp;
       Create table super_item as 
         select distinct * from super_item_temp;
       insert into super_item 
    select old_array, 
    array_sort_unique( array_agg(added_item) || a.old_array) as new_array, 
    array_length(array_sort_unique( array_agg(added_item) || a.old_array),1)

    from (
            select 
    a.new_array as old_array,

    case when string_to_array(b.item2::text, ",")::int[] <@ a.new_array and string_to_array(b.item1::text, ",")::int[] <@ a.new_array then null
     when string_to_array(b.item2::text, ",")::int[] <@ a.new_array then b.item1 else b.item2 end  as added_item

    from

    super_item a 
    left join pairs b 

    on  string_to_array(b.item1::text, ",")::int[] <@ a.new_array or  string_to_array(b.item2::text, ",")::int[] <@ a.new_array /**any item from pair is in array**/
    where 1=1

    group by a.new_array, 2

    having sum(b.count)>10
    and sum(b.offset)<=0 
    and 
    case 
    when string_to_array(b.item2::text, ",")::int[] <@ a.new_array and string_to_array(b.item1::text, ",")::int[] <@ a.new_array then null
    when string_to_array(b.item2::text, ",")::int[] <@ a.new_array then b.item1 else b.item2 end is not null
    /**new item is not null**/
    order by 2 desc
    )a
    group by 1;
    end loop;
end $length$ LANGUAGE plpgsql;

Postgresql function If condition then run query else stop

1 Answers