2

I have created this table with Postgresql 9.3:

CREATE TABLE usuarios.token_usrpwd
(
 id_token serial NOT NULL,
 token character varying,
 fechahora_creacion character varying,
 CONSTRAINT token_usrpwd_pkey PRIMARY KEY (id_token)
)
WITH (
OIDS=FALSE
);
ALTER TABLE usuarios.token_usrpwd
OWNER TO postgres;

Then I created the function:

CREATE OR REPLACE FUNCTION eiel.insert_usuario_tokenpwd(character varying, character varying)
 RETURNS boolean AS
$BODY$
DECLARE
 token_obtenido ALIAS FOR $1;
 fechahora_obtenido ALIAS FOR $2;
BEGIN
 INSERT INTO usuarios.token_usrpwd (token,fechahora_creacion) VALUES (token_obtenido,fechahora_obtenido);
  IF found THEN
        RETURN TRUE;
  ELSE
        RETURN FALSE;
  END IF;   
 END;
 $BODY$
   LANGUAGE plpgsql

Function and the table are both created correctly. Then when I call the function from the SQL console it inserts the row without problem:

SELECT eiel.insert_usuario_tokenpwd('190e526a2a54ff915d735026f9b64d8f','20150603112531')

But the problem comes when I call the same query from my php:

$insql= "SELECT eiel.insert_usuario_tokenpwd('190e526a2a54ff915d735026f9b64d8f','20150603112531')";
$queryresult = pg_query($database_connection, $insql) or die("Error in query: $insql . " . pg_last_error($database_connection));

Then an error happens and says :

Warning: pg_query(): Query failed: ERROR: no existe la función eiel.insert_usuario_tokenpwd(unknown, unknown) LINE 1: SELECT eiel.insert_usuario_tokenpwd('190e526a2a54ff915d73502... ^ HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos. in F:\ms4w\apps\EIELv3\xfunctions.php on line 432
Error in query: SELECT eiel.insert_usuario_tokenpwd('190e526a2a54ff915d735026f9b64d8f','20150603112531') . ERROR: no existe la función eiel.insert_usuario_tokenpwd(unknown, unknown) LINE 1: SELECT eiel.insert_usuario_tokenpwd('190e526a2a54ff915d73502... ^ HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos.

That means that the function does not exist.

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I had looked for info at this forum and others and tryed solutions like adding casts :: character varying or giving solutions like:

Postgres function creates but does not execute

or

No function matches the given name and argument types

but still doesn’t works. Any idea what is happening or if I am missing something?

Any idea would be appreciated

Thanks in Advance.

Community
  • 1
  • 1
J Belloch
  • 39
  • 6
  • Since the same query is successful in psql, check for what differs in php. That could be the user you're connected with, or the db itself. It's in the context rather than the query. Another possibility is that you created the function inside a transaction in psql and doesn't commit it. That transaction can see the function but no other can. – Daniel Vérité Jun 03 '15 at 11:01
  • I already have functions with SELECT and UPDATE that works both in psql and PHP, the problem its only happening when I started to use INSERT functions. I am not sure what you mean about transactions but gona investigate it – J Belloch Jun 03 '15 at 11:15
  • Just to be clearer, I was not suggesting to investigate what differs **in general** between php and the psql console because nothing does, it's irrelevant for the SQL engine. I was suggesting to double-check the particular context (dbname, username, permissions) for this specific call failure. – Daniel Vérité Jun 03 '15 at 11:35
  • Yes, what I mean is that in my php I already call to functions like the one is not working for SELECT or UPDATE with the same dbname, username, schemas... and they works. – J Belloch Jun 03 '15 at 11:46
  • Presumably the PHP frontend uses another type for the string literals than the function assumes. You should cast to `text` somewhere, probably in the .php. – wildplasser Jun 03 '15 at 12:01
  • 1
    The problem is solved. You were right @Daniel Vérité, there was a problem with DB permissions and access to the function from PHP. Thank you both for the help! – J Belloch Jun 04 '15 at 11:22

0 Answers0