Postgres Pro
Downloads
Home   >   mailing lists

Thread: I don't know what to do!

I don't know what to do!

From
"Cristian Prieto"
Date:
Hi, I wrote a few days ago with an error in a PostgreSQL 8.01 SP in pgplsql, I've done a lot of stuff and i still can't execute the sp, I will pass you all the messages...
 
-- **** My SP****  ---
CREATE OR REPLACE FUNCTION sp_insert_users_2(varchar, varchar, varchar, varchar, smallint, date, smallint, smallint, varchar, varchar, varchar,
 varchar, varchar, varchar, varchar) RETURNS integer AS '
 
DECLARE
        nombre ALIAS FOR $1;
        apellido ALIAS FOR $2;
        pass ALIAS FOR $3;
        e_mail ALIAS FOR $4;
        sexo ALIAS FOR $5;
        fecha_creacion ALIAS FOR $6;
        status_user ALIAS FOR $7;
        edad ALIAS FOR $8;
        pais_origen ALIAS FOR $9;
        ocupacion_user ALIAS FOR $10;
        estado_civil_user ALIAS FOR $11;
        ip ALIAS FOR $12;
        comentario_user ALIAS FOR $13;
        intereses_user  ALIAS FOR $14;
        foto            ALIAS FOR $15;
        userid INTEGER := nextval();
BEGIN
                BEGIN
                        INSERT INTO users (firstname,lastname,password,email,sex,datecreate,status,age,pais,ocupacion,estado_civil,remote_ip,co
mentario,intereses,fotoext) VALUES (nombre,apellido,pass,e_mail,sexo,fecha_creacion,status_user,edad,pais_origen,ocupacion_user,estado_civil_us
er,ip,comentario_user,intereses_user,foto);
                EXCEPTION
                        WHEN UNIQUE_VIOLATION THEN
                                RETURN 0;
                END;
                RETURN userid;
END;
' LANGUAGE plpgsql;
 
-- **** END of my SP **** --
 
Inside psql I did the following (insert.sql contains the sp definition) :
 
[cristian@desarrollo ~]$ psql mydb
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
mydb=# \i insert.sql
CREATE FUNCTION
mydb=# select sp_insert_users_2('Cristian', 'Prieto', 'hola', 'testing@something.org', 0, current_date, 0, 20, 'Guatemala', 'nadologo', 's
oltero', '200.49.160.1', 'hola', 'nada', 'fotoaqui');
ERROR:  function sp_insert_users_2("unknown", "unknown", "unknown", "unknown", integer, date, integer, integer, "unknown", "unknown", "unknown"
, "unknown", "unknown", "unknown", "unknown") does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
mydb=#
 
What is happening here? I just passed the right parameters but that stuff doesn't work handling my parameters? what am I doing wrong??? I have a week with that trouble, somebody please help me!
 
Thanks a lot...

Re: I don't know what to do!

From
John DeSoi
Date:
On Feb 14, 2005, at 10:35 AM, Cristian Prieto wrote:

> What is happening here? I just passed the right parameters but that
> stuff doesn't work handling my parameters? what am I doing wrong??? I
> have a week with that trouble, somebody please help me!


Look at this part of the parameter list:

... smallint, date, smallint, smallint, ...

here is what you passed:

...  0, current_date, 0, 20,

And here is the error:

ERROR:  function sp_insert_users_2(... integer, date, integer, integer,
...) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

PostgreSQL by default assumes 0 and 20 are integers not smallint as
your function expects. So try to change your function to use integer
instead of smallint or cast the parameters like this:

0::smallint, current_date, 0::smallint, 20::smallint


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL