postgresql - how to send a table parameter in a function?

0

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

    
asked by nasck 02.03.2018 в 16:45
source

1 answer

0

To pass a table as a parameter to a function you must use TYPE to define your table as a new data type . Read the documentation Here

    
answered by 02.03.2018 в 17:49