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]

Post by 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?

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

[lmanorders]

Post by Tom Lane

Post by 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?

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!