PARAMETER TABLE IN A POSTGRESQL FUNCTION

0

I am trying to send a table as a parameter in a postgresql function, and in visual basic I get the following error:

  

"There is no assignment of object type System.Data.DataTable to a native type of a known managed provider."

this is the postgresql code:

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,
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 

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$;

this is the code in vb:

Sub ADMINISTRAR_ARTEFACTO(ByVal P As E_ARTEFACTO)
    Dim CONECTION As New OdbcConnection
    Try
        CONECTION.ConnectionString = ConfigurationManager.ConnectionStrings("ALURHE_DBConnectionString_64").ConnectionString + ";pwd=2007211050"
        Dim COMMAND As OdbcCommand = New OdbcCommand("{call SP_ADM_ARTEFACTO (?,?,?,?,?,?,?,?)}", CONECTION)

        With COMMAND

            .Parameters.AddWithValue("V_SERIE", P.P_SERIE)
            .Parameters.AddWithValue("V_TIPO_ARTEFACTO", P.P_TIPO_ARTEFACTO)
            .Parameters.AddWithValue("V_MODELO", P.P_MODELO)
            .Parameters.AddWithValue("V_MARCA", P.P_MARCA)

            .Parameters.AddWithValue("V_DETALLE", P.P_DETALLE)
            .Parameters.AddWithValue("V_USUARIO", P.P_USUARIO)
            .Parameters.AddWithValue("V_ID_ARTEFACTO", If(P.P_TIPO_OPERACION = "M", P.P_ID_ARTEFACTO, DBNull.Value))
            .Parameters.AddWithValue("V_TIPO_OPERACION", P.P_TIPO_OPERACION)


        End With


        'If P.P_TIPO_OPERACION = "M" Then
        '    COMMAND.Parameters.AddWithValue("@ID_PRODUCTO", P.P_ID_PRODUCTO)
        'End If

        CONECTION.Open()
        COMMAND.ExecuteNonQuery()
        CONECTION.Close()

    Catch ex As Exception
        If (CONECTION.State = ConnectionState.Open) Then
            CONECTION.Close()
        End If

        Throw ex
    End Try
End Sub

I tried to try calling only the function as follows:

SELECT sp_adm_artefacto('1321321',1,'F-14',1,(1,10,5,50),1,NULL,'I')

and I get the error:

ERROR:  no existe la relación «v_detalle»
LINE 3: ...NTE,PRECIO,CANTIDAD,SUB_TOTAL,v_usuario,NOW() FROM V_DETALLE
                                                          ^
QUERY:  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
CONTEXT:  función PL/pgSQL sp_adm_artefacto(character varying,integer,character varying,integer,type_detalle_ac,integer,integer,character) en la línea 9 en sentencia SQL
SQL state: 42P01
    
asked by nasck 01.03.2018 в 23:00
source

0 answers