stored procedure postgres and php

Question

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. You might need to add explicit type casts.

Any insights please. Thanks.

Does the function visible within the schema that you are connected ? — Luc M, Oct 03 '11 at 16:59
@LucM - I used navicat lite and yes it is visible in functions under the schema main. — planet x, Oct 03 '11 at 17:03
Why are you using `$pg_query` instead of `pg_query`...? It's a [function variable](http://php.net/functions.variable-functions) I think. You can check [this question](https://stackoverflow.com/questions/4891872/function-and-variable) too. — Shivam Sharma, Dec 17 '18 at 14:43

Erwin Brandstetter · Accepted Answer · 2011-11-27T22:11:55.907

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;
```

stored procedure postgres and php

1 Answers1

Edit: