Re: how to proccess record returning null

What I want to do is,



CREATE OR REPLACE FUNCTION clean()
  RETURNS void AS $$
        DECLARE
        r record;
        BEGIN
        FOR r in select distinct(id) from temp
        loop
                  If r.id is null or r.id ='' Then
                        Insert into table_1 select * from temp;
                  else
                        Insert into table_1 select * from temp where
sequence_number=r.id;
                 end if;
        end loop;
        END;
  $$ LANGUAGE plpgsql;



If 'id' is null then I want to insert all the records from table 'temp' to
'table_1'
But if 'id' is not null I want to insert the data corresponding to those
id's from table 'temp' to 'table_1'.


Since id's are null or empty sting, control is not going inside the loop and
as a result no data is inserted from table 'temp' to table 'table_1'.

Currently I have come up with following workaround where null value is
checked separately out of loop.

CREATE OR REPLACE FUNCTION clean()
  RETURNS void AS $$
        DECLARE
        r record;
        BEGIN
        if (select distinct(id) from temp ) is null
        then    Insert into table_1 select * from temp;
        end if;

        FOR r in select distinct(id) from temp
        loop
                         Insert into table_1 select * from temp where
sequence_number=r.id;
         end loop;
        END;
  $$ LANGUAGE plpgsql;


But I think this is inefficient way of coding.
Do you have any better alternative to this ?

I hope I am clear.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-proccess-record-returning-null-tp5723932p5724361.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.