Re: Error while calling proc with table type from Application (npgsql)

aditya desai <admad123@gmail.com>	6:32 PM (19 minutes ago)
to Justin, Pgsql

Hi Justin,

Thanks for  your response. We have a user defined type created as below and we need to pass this user defined parameter to a procedure from .net code. Basically the procedure needs to accept  multiple rows as parameters(user defined table type). This happened seamlessly in SQL Server but while doing it in Postgres after migration we get the error mentioned in the above chain. Is theere any way we can achieve this?

CREATE TYPE public.optiontype AS (

projectid integer,

optionid integer,

phaseid integer,

remarks text

);

Also here is a sample procedure.

 CREATE OR REPLACE procedure SaveAssessmentInfo

 (

  p_Optiontable OptionType

 )

 

LANGUAGE 'plpgsql'

AS $BODY$ 

BEGIN

        

  insert into tempOptions

  select * from p_Optiontable;

END

 

 END;

 $BODY$;

Regards,

Aditya.

On Fri, Apr 30, 2021 at 6:32 PM aditya desai <admad123@gmail.com> wrote:

Hi Justin,

Thanks for  your response. We have a user defined type created as below and we need to pass this user defined parameter to a procedure from .net code. Basically the procedure needs to accept  multiple rows as parameters(user defined table type). This happened seamlessly in SQL Server but while doing it in Postgres after migration we get the error mentioned in the above chain. Is theere any way we can achieve this?

CREATE TYPE public.optiontype AS (

projectid integer,

optionid integer,

phaseid integer,

remarks text

);

Regards,

Aditya.

On Thu, Apr 29, 2021 at 6:32 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Thu, Apr 29, 2021 at 02:52:23PM +0530, aditya desai wrote:
> Hi,
> One of the procs which accept tabletype as parameter gives below error
> while being called from Application. Could not find a concrete solution for
> this. Can someone help?
>
> call PROCEDURE ABC (p_optiontable optiontype)

What is PROCEDURE ABC ?  If you created it, send its definition with your problem report.

> Below is the error while executing proc -

How are you executing it?  This seems like an error from npgsl, not postgres.
It may be a client-side error, and it may be that the query isn't even being
sent to the server at that point.

> “the clr type system.data.datatable isn't natively supported by npgsql or
> your postgresql. to use it with a postgresql composite you need to specify
> datatypename or to map it, please refer to the documentation.”

Did you do this ?
https://www.npgsql.org/doc/types/enums_and_composites.html

--
Justin