Обсуждение: Hello...

Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
Linux Fedora Core 3.
The tables and objects in the database schema are working ok...
I was just writing that function:

--- Begin of my function ---
CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varchar,
varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
varchar, varchar, varchar, varchar, varchar) RETURNS integer AS
$body$
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;
    returnValue integer;
    idusr INTEGER := nextval('users_iduser_seq');

BEGIN
    BEGIN
        INSERT INTO users (iduser,
firstname,lastname,password,email,sex,datecreate,status,age,pais,ocupacion,e
stado_civil,remote_ip,comentario,intereses,fotoext) VALUES (idusr,
nombre,apellido,pass,e_mail,sexo,fecha_creacion,status_user,edad,pais_origen
,ocupacion_user,estado_civil_user,ip,comentario_user,intereses_user,foto);
    EXCEPTION
        WHEN unique_violation then
            return 0;
    END;
    RETURN idusr;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
--- End of my functions ---

When I try to execute that function I get the following error:

ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown") does not exist

My table structure is as following:

--- Begin table structure ---
CREATE TABLE "public"."users" (
  "iduser" SERIAL,
  "firstname" VARCHAR(50) DEFAULT ''::character varying NOT NULL,
  "lastname" VARCHAR(50) DEFAULT ''::character varying NOT NULL,
  "password" VARCHAR(50) DEFAULT ''::character varying NOT NULL,
  "email" VARCHAR(80) DEFAULT ''::character varying NOT NULL,
  "datecreate" DATE DEFAULT '1970-01-01'::date NOT NULL,
  "status" SMALLINT DEFAULT (0)::smallint NOT NULL,
  "age" SMALLINT DEFAULT (0)::smallint NOT NULL,
  "lastlogin" DATE DEFAULT '1970-01-01'::date NOT NULL,
  "pais" VARCHAR(100) DEFAULT ''::character varying NOT NULL,
  "ciudad" VARCHAR(100),
  "car" CHAR(2),
  "where_inet" VARCHAR(50),
  "empleados" VARCHAR(50),
  "inet" VARCHAR(50),
  "pay" VARCHAR(50),
  "cada_cuanto" VARCHAR(50),
  "ocupacion" VARCHAR(150),
  "estado_civil" VARCHAR(50),
  "paish" VARCHAR(50),
  "remote_ip" VARCHAR(50),
  "zipcode" VARCHAR(50) DEFAULT ''::character varying,
  "votos" BIGINT DEFAULT (0)::bigint NOT NULL,
  "comentario" VARCHAR(4000),
  "puntaje" BIGINT DEFAULT (0)::bigint NOT NULL,
  "sex" SMALLINT DEFAULT 0 NOT NULL,
  "intereses" VARCHAR(150) DEFAULT ''::character varying,
  "paisorigen" VARCHAR(100),
  "fotoext" VARCHAR(10),
  "promedio" REAL DEFAULT 0.00,
  CONSTRAINT "users_email_key" UNIQUE("email"),
  CONSTRAINT "users_iduser_key" UNIQUE("iduser"),
  CONSTRAINT "users_pkey" PRIMARY KEY("iduser", "email")
) WITH OIDS;
--- End table structure ---

What is wrong with my function or parameters? I was just looking around a
lot of places in internet and I could not get any answer, help please!

On Fri, Feb 11, 2005 at 02:56:09PM -0600, Cristian Prieto wrote:
> Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
> Linux Fedora Core 3.
> The tables and objects in the database schema are working ok...
> I was just writing that function:

Here's the function definition:

> CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varchar,
> varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
> varchar, varchar, varchar, varchar, varchar) RETURNS integer AS

As here's your error message:

> ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", "unknown", "unknown") does not exist

It's looking for a function which accepts and integer in the 7th column
you havn't define one. Check your function call...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog@svana.org> writes:
> Here's the function definition:

>> CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varch=
> ar,
>> varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
>> varchar, varchar, varchar, varchar, varchar) RETURNS integer AS

> As here's your error message:

>> ERROR:  function public.sp_insert_users_new("unknown", "unknown", "unknow=
> n",
>> "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
>> "unknown", "unknown", "unknown", "unknown", "unknown") does not exist

> It's looking for a function which accepts and integer in the 7th column
> you havn't define one. Check your function call...

The function call probably looks like ..., 42, ... and since integer
doesn't implicitly cast to smallint, it doesn't match.  It would be
smarter to declare the function as taking integer; if you really want
it to take smallint, you'll need to quote or cast the arguments.

            regards, tom lane