Thread: Error while calling proc with table type from Application

Error while calling proc with table type from Application

From
aditya desai
Date:
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)

 

Below is the error while executing proc -

“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.”


Regards,

Aditya.

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

From
Justin Pryzby
Date:
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



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

From
aditya desai
Date:
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

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

From
aditya desai
Date:

aditya desai <admad123@gmail.com>

6:32 PM (19 minutes ago)
to JustinPgsql
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

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

From
Vijaykumar Jain
Date:
are you referring to this 

I have not used/heard a  similar select * from custom_type 
so just trying to help :)

// src table
postgres=# \d tt
                 Table "public.tt"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 x      | integer |           |          |
 y      | text    |           |          |

// dst table
postgres=# \d tt_clone
              Table "public.tt_clone"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 x      | integer |           |          |
 y      | text    |           |          |

// function to insert into dst table tt_clone from src tt
//tables input as tt table rowtype
CREATE OR REPLACE function tt_fn (myrow tt) returns void
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN

        insert into tt_clone(x, y)
        select myrow.x, myrow.y;

END;
$BODY$;
CREATE FUNCTION

postgres=# select tt_fn(tt.*) from tt;
 tt_fn
-------


(2 rows)


I am not sure how to do that with "call proc" for stored procedures.

i think it may have to be tweaked to either make the input param variadic or  an array and loop through rows
like this 

i guess some should be able to help given there is more context to your query.

Thanks,
Vijay


On Fri, 30 Apr 2021 at 18:53, aditya desai <admad123@gmail.com> wrote:

aditya desai <admad123@gmail.com>

6:32 PM (19 minutes ago)
to JustinPgsql
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