Thread: 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.