How to determine if a function exists

[lmanorders]

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

 

[Tom Lane]

"lmanorders" <lmano...@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


--
Sent via pgsql-novice mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[lmanorders]

> "lmanorders" <lmano...@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!