November 13th, 2013, 01:59 PM
A strange procedure bug?
Hi there, I'm working on a project involving a postgresql database and I run into a strange error that I'm not sure how to resolve.
I have two basic queries done differently that return the same row.
-- base queries returning a row I'm looking for
select * from medscheck where medscheck_id = 3;
select * from medscheck where resident_id = 73373 limit 1 ;
I use these queries in the stored procedure, first query works and the second one doesn't. Have no idea how that's possible unless there is some underlying corruption in the table. Not sure how to fix that considering the table is quite large and with many FK constrains.
-- the variable gets a row assigned to it
select into _last_annual_completed * from medscheck where medscheck_id = 1574;
-- Fails, the variable contains an empty row without data
select into _last_annual_completed * from medscheck where resident_id = 73373 limit 1 ;
Any ideas or help would be greatly apprecited, thank you very much.
November 14th, 2013, 04:28 AM
Are you sure there is a record with residentid=73373?
What does FOUND say after you've run the query?
And does the other query begin to fail when you add LIMIT 1 to it?