Обсуждение: how to proccess record returning null
Hi all, I am new to postgresql and I am trying to write a function which uses record (r) to select from table and then proccess that record value. CREATE OR REPLACE FUNCTION clean() RETURNS integer AS $$ DECLARE r record; result integer:= 0; BEGIN FOR r in select distinct(id) from temp loop IF r.id is null or r.id ='' THEN result := 555; else result := 999; end if; end loop; RETURN result; END; $$ LANGUAGE plpgsql; The problem here is 'r' is returning no records and I want to set result to 555 if r.id is null. The function is created successfully but the result select clean() is always '0'. I am unable to spot the problem. May be this is trivial but I am struggling for it. Can anybody suggest anything? -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-proccess-record-returning-null-tp5723932.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On 09/13/12 12:17 PM, te wrote: > FOR r in select distinct(id) from temp > .... > select clean() what is this selecting records from? what is "temp" ? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Sorry I didnt mention it. temp is a table name. FOR r in select distinct(id) from temp There are no distinct id's in table 'temp', so record 'r' has null values. So I guess the control is not going inside the loop. Any suggestions? -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-proccess-record-returning-null-tp5723932p5724061.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On 09/14/12 8:31 AM, te wrote: > Sorry I didnt mention it. > > temp is a table name. > > FOR r in select distinct(id) from temp > > There are no distinct id's in table 'temp', so record 'r' has null values. > > So I guess the control is not going inside the loop. > > Any suggestions? there's a difference between zero records, and a record containing null fields.. yes, if there are no records, the loop will execute zero iterations, and result will be integer zero. as coded, for each record, it will test if id is null or an empty string, the result value will be that of the last distinct(id); since no id is likely to be null or an empty string, the odds are, it will be returning 999 if there are any records and 0 if there aren't any. what exactly do you WANT to do? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
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.