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