Problem with Postgresql function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
barb woolums
Forum Contributor
Posts: 134
Joined: Sun Feb 08, 2009 9:52 pm

Problem with Postgresql function

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problem with Postgresql function

Post 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 ;) ).
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
barb woolums
Forum Contributor
Posts: 134
Joined: Sun Feb 08, 2009 9:52 pm

Re: Problem with Postgresql function

Post by barb woolums »

Thanks all fixed!!
Post Reply