[SQL] error in function!!
Hi,
i have a problem with the following
sentence:
CREATE OR REPLACE FUNCTION
tschema.sp_actualizar_contacto(p_idpers
text,
p_nombre
text,
p_apellido
text,
p_titulo
text,
p_fecnac
text,
p_codedociv integer,
p_sexo text,
p_codpais
integer,
p_pw text,
p_empr text,
p_cargo text,
p_pwempr
text,
p_aniv text,
p_prof text,
p_trab text,
p_fecgen text,
p_fecing
text,
p_fuente
text,
p_codupload
integer,
cli_codigo integer ) RETURNS text as '
DECLARE
c_codigo
integer;
c_sinc
varchar;
c_idpers ALIAS for
$1; c_nombre
ALIAS for $2; c_apellido
ALIAS for $3;
c_titulo ALIAS for
$4; c_fecnac
ALIAS for $5; c_codedociv ALIAS
for $6;
c_sexo ALIAS for
$7; c_codpais ALIAS
for $8;
c_pw
ALIAS for $9;
c_empr ALIAS for
$10;
c_cargo ALIAS for
$11; c_pwempr
ALIAS for $12;
c_aniv ALIAS for
$13;
c_prof ALIAS for
$14;
c_trab ALIAS for
$15; c_fecgen
ALIAS for $16;
c_fecing ALIAS for
$17; c_fuente
ALIAS for $18; c_codupload ALIAS
for $19; cli_Codigo ALIAS
for $20;BEGIN select
nextval(''seq_tbu_contacto_cont_codigo'') into
c_codigo;
Insert into tbu_contacto (cont_codigo,
cont_idpers,cont_nombre,cont_apellido,cont_titulo,cont_fecnac,cont_codedociv,cont_sexo,cont_codpais,cont_pw,cont_empr,cont_cargo,cont_pwempr,cont_aniv,cont_prof,cont_trab,cont_fecgen,cont_fecing,cont_fuente,cont_sinc,cont_codupload)
values
(c_codigo,c_idpers,c_nombre,c_apellido,c_titulo,to_timestamp(c_fecnac,''-mm-dd
HH:MM:SS''),c_codedociv,c_sexo,1,c_pw,c_empr,c_cargo,c_pwempr,c_aniv,c_prof,c_trab,to_timestamp(c_fecgen,''-mm-dd
HH:MM:SS''),to_timestamp(CURRENT_TIMESTAMP,''-mm-dd HH:MM:SS'')
,c_fuente,''S'',c_codupload);
return ''OK:''||c_codigo;
END;' LANGUAGE 'plpgsql'
this is the error in pgadmin III on windows
(postgresql 8.0):
ERROR: function
tschema.sp_actualizar_contacto(integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown",
"unknown", integer, integer) does not existHINT: No function matches
the given name and argument types. You may need to add explicit type
casts.
I need to know if the "insert sentence" was
sucesfull, how do i do?
help me with this please,
thanks!!
[SQL] Function .. AS..?
Those instructions are good but i want to call this function only for " select consulta_contacto(1)" nothing more, Is really necesary to use "AS ..."? this is the call: select * from consulta_contacto(1) as (cont_codigo integer, cont_idpers varchar, cont_nombre varchar, cont_apellido varchar, cont_titulo varchar, cont_fecnac timestamp , cont_codedociv integer, cont_sexo char(1), cont_codpais integer, cont_pw varchar, cont_empr varchar, cont_cargo varchar, cont_pwempr varchar, cont_aniv char(5), cont_prof varchar, cont_trab char(1), cont_fecgen timestamp, cont_fecing timestamp, cont_fuente char(1), cont_sinc char(1), cont_codupload integer); this is the function: CREATE OR REPLACE FUNCTION consulta_contacto(integer) RETURNS SETOF RECORD AS 'DECLARE rec RECORD; sup INTEGER;BEGIN FOR rec IN SELECT cont_codigo,cont_idpers, cont_nombre,cont_apellido,cont_titulo,cont_fecnac,cont_codedociv,cont_sexo,cont_codpais,cont_pw,cont_empr,cont_cargo,cont_pwempr,cont_aniv,cont_prof,cont_trab,cont_fecgen,cont_fecing,cont_fuente,cont_sinc,cont_codupload FROM tbu_contacto LOOP RETURN NEXT rec ; END LOOP; RETURN ;END;' LANGUAGE plpgsql;
Re: [SQL] Replacing a table with constraints
Use Drop table YOUR_TABLE cascade Jhon CarrilloIngeniero en ComputaciónCaracas - Venezuela - Original Message - From: Mark Fenbers To: pgsql-sql@postgresql.org Sent: Friday, May 13, 2005 2:38 PM Subject: [SQL] Replacing a table with constraints I have a table called Counties which partially contains a lot bad data. By" bad data", I mean some records are missing; some exist and shouldn't; and some records have fields with erroneous information. However, the majority of the data in the table is accurate. I have built/loaded a new table called newCounties with the same structure as Counties, but contains no bad data. My was to completely replace the contents of Counties with the contents of newCounties. The problem is: several other tables have Foreign Key constraints placed on Counties. Therefore, Pg will not let me 'DELETE FROM Counties;", nor will it let me "DROP TABLE Counties;" I'm perplexed. Can someone suggest how I can best get data from Counties to look just like newCounties?Mark ---(end of broadcast)---TIP 8: explain analyze is your friend
[SQL] unsuscribe
unsuscribe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
