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...
