I am using postgresql, I would like to know how to send a table parameter in a function.
I have already created the type type_detalle_ac:
CREATE TYPE public.type_detalle_ac AS
(
id_componente integer,
precio numeric(10,2),
cantidad integer,
sub_total numeric(10,2)
);
and I use it in the following function:
CREATE OR REPLACE FUNCTION public.sp_adm_artefacto(
v_serie character varying DEFAULT NULL::character varying,
v_tipo_artefacto integer DEFAULT NULL::integer,
v_modelo character varying DEFAULT NULL::character varying,
v_marca integer DEFAULT NULL::integer,
v_detalle type_detalle_ac DEFAULT NULL::type_detalle_ac,
v_usuario integer DEFAULT NULL::integer,
v_id_artefacto integer DEFAULT NULL::integer,
v_tipo_operacion character DEFAULT NULL::bpchar)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 0
AS $BODY$
DECLARE
VL_ID_ARTEFACTO INTEGER;
BEGIN
IF V_TIPO_OPERACION = 'I' THEN
INSERT INTO DETALLE_AC(ID_DETALLE_AC,ID_ARTEFACTO,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,
USUARIO_CREACION,FECHA_CREACION)
SELECT 1,1,ID_COMPONENTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE;
END IF;
END;
$BODY$;
But when calling the function:
SELECT "sp_adm_artefacto"('1321321',1,'F-14',1,(1,10,5,50),1,NULL,'I')
but I get an error:
ERROR: no existe la relación «v_detalle»
LINE 3: ...NTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE
thanks