Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to determine if a function exists

How to determine if a function exists

From
"lmanorders"
Date:
I'm attempting to determine if a stored procedure exists. If it doesn't, then I want to create it. I'm developing in C++ using the libpq interface. I found the following suggestion in the docs to determine if a function exists or not. I tried creating what I think is an identical query in pgAdmin and it seems to work ok. Can anyone tell me why the following always returns PGRES_TUPLE_OK, whether the function exists or not?
 
 strcpy(command, "SELECT * FROM pg_proc WHERE proname = 'getdetailamtsacct'");
 pqRes = PQexec(conn, command);
 if (PQresultStatus(pqRes) != PGRES_TUPLES_OK)
 {
  // translate the status code into a string
  pqExSt = PQresultStatus(pqRes);
  strcpy(result, PQresStatus(pqExSt));
  // display the status message
  ...
  exist = false;  // table doesn't exist
  }
  if (!exist)
  {
    // create the function (stored procedure)
  }
 
Is there a better way to determine if a function already exists?
 
Thanks, Lynn
 

Re: How to determine if a function exists

From
Tom Lane
Date:
"lmanorders" <lmanorders@gmail.com> writes:
> I'm attempting to determine if a stored procedure exists. If it doesn't, then I want to create it. I'm developing in
C++using the libpq interface. I found the following suggestion in the docs to determine if a function exists or not. I
triedcreating what I think is an identical query in pgAdmin and it seems to work ok. Can anyone tell me why the
followingalways returns PGRES_TUPLE_OK, whether the function exists or not? 

PGRES_TUPLES_OK means you successfully executed a SELECT (or other
command capable of returning tuples).  It doesn't imply anything about
how many tuples were returned.  In this case, you'd want to check for
PQntuples() > 0, as well.  And perhaps think about what you'd do if
you got more than one match, which is quite possible in view of function
overloading, schema search path, etc.

            regards, tom lane

Re: How to determine if a function exists

From
"lmanorders"
Date:
> "lmanorders" <lmanorders@gmail.com> writes:
>> I'm attempting to determine if a stored procedure exists. If it doesn't,
>> then I want to create it. I'm developing in C++ using the libpq
>> interface. I found the following suggestion in the docs to determine if a
>> function exists or not. I tried creating what I think is an identical
>> query in pgAdmin and it seems to work ok. Can anyone tell me why the
>> following always returns PGRES_TUPLE_OK, whether the function exists or
>> not?
>
> PGRES_TUPLES_OK means you successfully executed a SELECT (or other
> command capable of returning tuples).  It doesn't imply anything about
> how many tuples were returned.  In this case, you'd want to check for
> PQntuples() > 0, as well.  And perhaps think about what you'd do if
> you got more than one match, which is quite possible in view of function
> overloading, schema search path, etc.
>
> regards, tom lane
>

Thanks. That works great!