stored procedure postgres and php

I am currently having trouble in regards of my stored procedure.

I have this stored procedure, under the schema main:

register(LOGIN_ID varchar, PASSWORD varchar)

I am trying to call this in a query but I got no luck,

$query = "SELECT main.register('test','pass123')";
$run = $pg_query($conn, $query);

This returns an error that No function matches the given name and argument types. 开发者_如何学JAVAYou might need to add explicit type casts.

Any insights please. Thanks.

---

Try explicit cast:

$query = "SELECT main.register('test'::varchar,'pass123'::varchar)";

Edit:

Diagnose your problem with this query and report back the ouput.
Find functions in all schemas in your database:

SELECT n.nspname, p.proname, pg_catalog.pg_get_function_arguments(p.oid) as params
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname ~~* '%my_function_name_here%'
AND    pg_catalog.pg_function_is_visible(p.oid);

Use the same role to connect! Demo output:

 nspname |  proname | params
---------+----------+----------------------------------------
 public  | register | string text
 public  | register | login_id varchar, password varchar
 main    | register | string text, form text, maxlen integer

Here is Dee's output:

 nspname |  proname | params
---------+----------+----------------------------------------
 (0 rows)

Obviously, your role cannot see any functions of the name register with any parameters in any schema. Try the same as superuser - postgres in most cases.

• If you still see nothing, the function is just not there. You need to create it or you have a typo somewhere.

• If you see it as superuser, then the role you are connecting with lacks the necessary privileges, most likely on the schema main. In this case, the cure would be (as superuser or owner of schema main):

  GRANT USAGE ON SCHEMA main TO my_role;