Page 1 of 1

Problem with Postgresql function

Posted: Wed Sep 30, 2009 10:13 pm
by barb woolums
I have a simple function set up to query my postgesql db here's the function

Code: Select all

 
CREATE OR REPLACE FUNCTION query_unit_owner_exists(bigint, bigint)
  RETURNS bigint AS
'SELECT id FROM unit_owner WHERE unit = $1 and owner=$2'
  LANGUAGE 'sql' VOLATILE
  COST 100;
 
when I run this function for a value pair that doesn't exist in the table, I would expect to get no rows returned, but instead I get 1 row with no value. Is there any way to stop this?

If I run the query itself it returns no rows as expected.

Re: Problem with Postgresql function

Posted: Thu Oct 01, 2009 2:57 am
by VladSun
First, I think, there had been an error shown when you tried to execute the code above. Like this:
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
You need to store the result of the query into a variable and return it.

Second, your query returns an integer not a row so it *will* always return a result (even if it's null).
You need to define your SP so it returns a rowset (even it's a single row), in case you want to have an empty rowset when rows are not found (that's what I would expect ;) ).

Re: Problem with Postgresql function

Posted: Thu Oct 01, 2009 3:40 am
by barb woolums
Thanks all fixed!!