Обсуждение: plpgsql always returning null..

hi guys
I am trying out a simple plpgsql function on 702

drop function quantity_available(int4);
CREATE FUNCTION quantity_available (int4) RETURNS INT4 AS '
DECLARE
    totq int4;
BEGIN
    select INTO totq cast(quantity as INT4) from inventory
    where exhibit_distribution_id=$1;
    return totq;
    END;'
LANGUAGE 'plpgsql';

select quantity_available(594);

is always returning null even when there is correponding data

arttoday=# select quantity from inventory where exhibit_distribution_id = 594;
quantity
----------
1
(1 row)

Can some one please explain this anamoly..

Thanks
Anand

I don't see this on my test on 7.1beta3, can you give the
table schema and some sample data?

On Fri, 2 Feb 2001, Anand Raman wrote:

> hi guys
> I am trying out a simple plpgsql function on 702
>
> drop function quantity_available(int4);
> CREATE FUNCTION quantity_available (int4) RETURNS INT4 AS '
> DECLARE
>     totq int4;
> BEGIN
>     select INTO totq cast(quantity as INT4) from inventory
>     where exhibit_distribution_id=$1;
>     return totq;
>     END;'
> LANGUAGE 'plpgsql';
>
> select quantity_available(594);
>
> is always returning null even when there is correponding data
>
> arttoday=# select quantity from inventory where exhibit_distribution_id = 594;
> quantity
> ----------
> 1
> (1 row)
>
> Can some one please explain this anamoly..
>
> Thanks
> Anand
>

hi

we have 2 databases on the test environment both running 702 ..
The answer for consitent in both the machines.. Both the databases when
queries returned null..

The table inventory is a very simple 2 column table containing
exhibit_distribution_id and quantity as the column..

arttoday=# select * from inventory;
 exhibit_distribution_id | quantity
-------------------------+----------
                     453 |       59
                     454 |       31
                     455 |       71
                     456 |       54
                     457 |       51
                     458 |       55
                     459 |       68
                     460 |       44
                     461 |       60
                     ..
                     ..
                     ..
                     594 |       70

Regards
Anand
On Fri, Feb 02, 2001 at 09:37:08AM -0800, Stephan Szabo wrote:
>
>I don't see this on my test on 7.1beta3, can you give the
>table schema and some sample data?
>
>On Fri, 2 Feb 2001, Anand Raman wrote:
>
>> hi guys
>> I am trying out a simple plpgsql function on 702
>>
>> drop function quantity_available(int4);
>> CREATE FUNCTION quantity_available (int4) RETURNS INT4 AS '
>> DECLARE
>>     totq int4;
>> BEGIN
>>     select INTO totq cast(quantity as INT4) from inventory
>>     where exhibit_distribution_id=$1;
>>     return totq;
>>     END;'
>> LANGUAGE 'plpgsql';
>>
>> select quantity_available(594);
>>
>> is always returning null even when there is correponding data
>>
>> arttoday=# select quantity from inventory where exhibit_distribution_id = 594;
>> quantity
>> ----------
>> 1
>> (1 row)
>>
>> Can some one please explain this anamoly..
>>
>> Thanks
>> Anand
>>

Okay, that's basically what I tried on my 7.1b3 system which
worked, both with quantity as int2 and int4.  It'll probably
work there.  In the mean time, what kinds of queries are you
using this in, maybe a subselect would work as a temporary fix.

On Sat, 3 Feb 2001, Anand Raman wrote:

> hi
>
> we have 2 databases on the test environment both running 702 ..
> The answer for consitent in both the machines.. Both the databases when
> queries returned null..
>
> The table inventory is a very simple 2 column table containing
> exhibit_distribution_id and quantity as the column..