1 Replies - 1264 Views - Last Post: 13 November 2012 - 01:20 PM Rate Topic: -----

#1 SilverMage  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 11-October 10

How to check if a tuple exists in a relation in PL/pgSQL

Posted 13 November 2012 - 12:34 PM

Hello, I'm having trouble creating a function that gets the statistics of a publisher. So far, I only have the part of the function that checks whether a publisher exists by checking all of the publisher codes in the publisher relation.

CREATE FUNCTION uspGetPubStatsDMF(pcode TEXT) RETURNS TEXT AS $$
BEGIN
	IF pcode NOT IN SELECT publishercode FROM publisher THEN 
	RAISE NOTICE 'Publisher code % does not exist.', pcode;
	END IF;
END	
$$ LANGUAGE 'plpgsql';



This is what the function looks like so far, but every time I try to execute in PGAdmin, I just get error messages and the following hint

ERROR:  function uspgetpubstatsdmf(unknown) does not exist
            LINE 1: SELECT uspGetPubStatsDMF('AH')
                           ^
            HINT:  No function matches the given name and argument types. You might need to add explicit type casts.



What seems wrong with my code?

Is This A Good Question/Topic? 0
  • +

Replies To: How to check if a tuple exists in a relation in PL/pgSQL

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: How to check if a tuple exists in a relation in PL/pgSQL

Posted 13 November 2012 - 01:20 PM

You're running into a problem that I ran into early on when using PostgreSQL. In pgSQL, function names are identifiers (like table or column names), and are therefore case sensitive. When an identifier is referenced without double quotes, the identifier is converted to lower case before being intepreted. So, your query...
SELECT uspGetPubStatsDMF('AH')


...is being interpreted as...
SELECT uspgetpubstatsdmf('AH')


...and since uspGetPubStatsDMF and uspgetpubstatsdmf are two different identifiers in pgSQL, you're getting that error. Try this instead:
SELECT "uspGetPubStatsDMF"('AH')


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1