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

are you referring to this
SQL SERVER User Defined Table Type and Table Valued Parameters - SqlSkull
<https://sqlskull.com/2020/01/04/sql-server-user-defined-table-type-and-table-valued-parameters/>

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
How to pass multiple rows to PostgreSQL function? - Stack Overflow
<https://stackoverflow.com/questions/43811093/how-to-pass-multiple-rows-to-postgresql-function>

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(at)gmail(dot)com> wrote:

>
> aditya desai <admad123(at)gmail(dot)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(at)gmail(dot)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(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
>>>
>>