POSTGRESQL PG/PGSQL- Function with params

Question

I'm having some issues making a function in Postgresql, I have this function:

CREATE OR REPLACE FUNCTION public.isp_ticket(_cr integer, _grupo character varying(255), _numero integer, _descripcion text, _resumen character varying(255), _fechaaper timestamp with time zone, _fechacierr timestamp with time zone, _tipo smallint, _apellidousuarioafectado character varying(255), _apellidosolicitante character varying(255), _tenant character varying(255), _metodoreportado character varying(100), _prioridad smallint, _sla character varying(255), _categoria character varying(255), _estado character varying(255), _herramienta_id integer, _asignado character varying(255), _nombresolicitante character varying(255), _nombreusuarioafectado character varying(255))
RETURNS void AS $$

BEGIN
     CASE 
         WHEN _asignado = '' AND _close_date = '' AND _sla = '' 
         THEN  INSERT INTO public.website_ticket(cr, grupo, numero, descripcion, resumen, fechaaper, tipo, apellidousuarioafectado, apellidosolicitante, tenant, metodoreportado, prioridad, categoria, estado, herramienta_id, nombresolicitante, nombreusuarioafectado) VALUES (_cr, _grupo, _numero, _descripcion, _resumen, _fechaaper, _tipo, _apellidousuarioafectado, _apellidosolicitante, _tenant, _metodoreportado, _prioridad, _categoria, estado, _herramienta_id, _nombresolicitante, _nombreusuarioafectado);
         WHEN _asignado = '' AND _close_date = ''
         THEN INSERT INTO public.website_ticket(cr, grupo, numero, descripcion, resumen, fechaaper, tipo, apellidousuarioafectado, apellidosolicitante, tenant, metodoreportado, prioridad, sla, categoria, estado, herramienta_id, nombresolicitante, nombreusuarioafectado) VALUES (_cr, _grupo, _numero, _descripcion, _resumen, _fechaaper, _tipo, _apellidousuarioafectado, _apellidosolicitante, _tenant, _metodoreportado, _prioridad, _sla, _categoria, _estado, _herramienta_id, _nombresolicitante, _nombreusuarioafectado);
         WHEN new_close_date = ''
         THEN INSERT INTO public.website_ticket(cr, grupo, numero, descripcion, resumen, fechaaper, tipo, apellidousuarioafectado, apellidosolicitante, tenant, metodoreportado, prioridad, sla, categoria, estado, herramienta_id, asignado,nombresolicitante, nombreusuarioafectado)
    VALUES (_cr, _grupo, _numero, _descripcion, _resumen, _fechaaper, _tipo, _apellidousuarioafectado, _apellidosolicitante, _tenant, _metodoreportado, _prioridad, _sla, _categoria, _estado, _herramienta_id, _asignado, _nombresolicitante, _nombreusuarioafectado);
ELSE
UPDATE public.website_ticket SET fechacierr = _fechacierr WHERE numero = _numero;
END CASE;
END;
$$ LANGUAGE plpgsql;

and when I try to use the function doing this:

SELECT public.isp_ticket(924266, 
'EUS_Zona V Region', 
512294,
'Nombre: Gisselle Espinoza Contreras\nCorreo: [email protected]   
\nAnexo: 6221\nUbicación: Valparaiso\nPais: Chile\nMotivo: Usuario indica 
que su computador se apagó repentinamente. Se pudo entrar a windows después 
de un buen rato, pero no puede ingresar a las aplicaciones que se conecten a 
red.\n\nDirección: Plaza Victoria 1646 - Piso 1 - Banco',
'Valparaiso // Computador con problemas de conexión.',
'2018-01-23 15:17:51',
'',
1,
'Espinoza Contreras',
'Espinoza Contreras',
'Ripley',
'Telephone',
3,
'',
'Ripley.Hardware.Desktop.Falla',
'Open',
1,
'',
'Gissel Rose Marie',
'Gissel Rose Marie')

I tried to CAST every value, and it didn't work either, always appear the same error:

ERROR: no existe la función public.isp_ticket(integer, character varying, integer, text, character varying, timestamp with time zone, unknown, integer, character varying, character varying, character varying, character varying, integer, unknown, character varying, character varying, integer, unknown, character varying, character varying) LINE 1: SELECT public.isp_ticket( ^ SQL state: 42883 Character: 8

I need help how can I fix it?

Forwards thanks everyone!!!

Adam · Accepted Answer · 2018-04-26 16:44:07Z

2

Parameter #7, _fechacierr should be timestamp with time zone. You can not pass '', change it to null (and cast it to timestamp with time zone) if you need empty value.

And it's worth to read how PostgreSQL finds specific function to call, especially:

If any input arguments are unknown, check the type categories accepted at those argument positions by the remaining candidates. At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.) Otherwise, if all the remaining candidates accept the same type category, select that category; otherwise fail because the correct choice cannot be deduced without more clues. Now discard candidates that do not accept the selected type category. Furthermore, if any candidate accepts a preferred type in that category, discard candidates that accept non-preferred types for that argument. Keep all candidates if none survive these tests. If only one candidate remains, use it; else continue to the next step.

answered Apr 26, 2018 at 16:44

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dibu Escobedo Over a year ago

it didn't work.... I delete _fechcierr value and CAST every value and it didn't work either... here is my code, and how I'm calling rextester.com/LWOTK18211

Adam Over a year ago

Also cast parameters #7 & #12 to smallint and it will work. I got 42P01: relation "public.website_ticket" does not exist ;)

Dibu Escobedo Over a year ago

I'm having this error SQL state: 42883 ERROR: no exists function public.isp_ticket(integer, character varying, integer, text, character varying, timestamp with time zone, integer, character varying, character varying, character varying, character varying, smallint, character varying, character varying, character varying, integer, character varying, character varying, character varying) LINE 1: SELECT public.isp_ticket(

Adam Over a year ago

See your forked query: rextester.com/LUQC87866 - I have only changed 2 lines and added casting to smallint. Those changes are marked in the comment.

Collectives™ on Stack Overflow

POSTGRESQL PG/PGSQL- Function with params

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related