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

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(at)telsasoft(dot)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
>