Thread: Error while calling proc with table type from Application
| 6:32 PM (19 minutes ago) |  | ||
| ||||
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
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>
> 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