I don't know what to do!
| От | Cristian Prieto |
|---|---|
| Тема | I don't know what to do! |
| Дата | |
| Msg-id | BAY13-DAV161668A3375F47AFF6FF1B06A0@phx.gbl обсуждение исходный текст |
| Ответы |
Re: I don't know what to do!
|
| Список | pgsql-general |
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 '
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;
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.
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
\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=#
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...
В списке pgsql-general по дате отправления: