pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction with table Valued Parameters execution issue
3+ messages / 2 participants
[nested] [flat]
* Function with table Valued Parameters execution issue
@ 2017-07-10 12:49 Kanjibhai.Kanzaria@thomsonreuters.com
0 siblings, 1 reply; 3+ messages in thread
From: Kanjibhai.Kanzaria@thomsonreuters.com @ 2017-07-10 12:49 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am new one in Postgres and am using pgAdmin III for postgres tools.
I have created data type which replicates data table in our application and created one function with table valued parameter. I have tried lots of solution but, am not able to achieved my goal.
I would like to know how to call or execute function with table value parameter in Postgres.
I have defined type like this:
CREATE TYPE "CategoryType" AS
("CATEGORY_NODE_ID" text,
"PARENT_ID" text,
"CODE" text,
"DESCRIPTION" text,
"SEQUENCE_NUMBER" integer,
"ACCOUNT_GROUP_ID" integer,
"FINANCIALREPORT_CATEGORY_ID" integer,
"FINANCIALREPORT_DETAIL_ID" integer);
ALTER TYPE "CategoryType"
OWNER TO postgres;
I have defined following function:
CREATE OR REPLACE FUNCTION "CategoryBulkImport"(_tbl_type "CategoryType")
RETURNS
TABLE (
"CATEGORY_NODE_ID" text,
"PARENT_ID" text,
"CODE" text,
"DESCRIPTION" text,
"SEQUENCE_NUMBER" integer,
"ACCOUNT_GROUP_ID" integer,
"FINANCIALREPORT_CATEGORY_ID" integer,
"FINANCIALREPORT_DETAIL_ID" integer
)
AS
$BODY$
SELECT "CATEGORY_NODE_ID", "PARENT_ID", "CODE", "DESCRIPTION", "SEQUENCE_NUMBER",
"ACCOUNT_GROUP_ID", "FINANCIALREPORT_CATEGORY_ID", "FINANCIALREPORT_DETAIL_ID"
FROM _tbl_type;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION "CategoryBulkImport"("CategoryType")
OWNER TO postgres;
Here I want to use table type parameter(_tbl_type) inside function with select statement but not able to access it so please suggest me a way.
Here I am not sure about my function so please correct me if I am wrong.
Thank you.
Best Regards,
Kanji Kanzariya
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function with table Valued Parameters execution issue
@ 2017-07-10 13:26 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Kanjibhai.Kanzaria@thomsonreuters.com
0 siblings, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2017-07-10 13:26 UTC (permalink / raw)
To: Kanjibhai.Kanzaria@thomsonreuters.com; pgsql-sql
On 07/10/2017 05:49 AM, Kanjibhai.Kanzaria@thomsonreuters.com wrote:
> Hello,
>
> I am new one in Postgres and am using pgAdmin III for postgres tools.
>
> I have created data type which replicates data table in our application
> and created one function with table valued parameter. I have tried lots
> of solution but, am not able to achieved my goal.
>
> I would like to know how to call or execute function with table value
> parameter in Postgres.
>
> I have defined type like this:
>
> CREATE TYPE "CategoryType" AS
>
> ("CATEGORY_NODE_ID" text,
>
> "PARENT_ID" text,
>
> "CODE" text,
>
> "DESCRIPTION" text,
>
> "SEQUENCE_NUMBER" integer,
>
> "ACCOUNT_GROUP_ID" integer,
>
> "FINANCIALREPORT_CATEGORY_ID" integer,
>
> "FINANCIALREPORT_DETAIL_ID" integer);
>
> ALTER TYPE "CategoryType"
>
> OWNER TO postgres;
>
> I have defined following function:
>
> CREATE OR REPLACE FUNCTION "CategoryBulkImport"(_tbl_type "CategoryType")
>
> RETURNS
>
> TABLE (
>
> "CATEGORY_NODE_ID" text,
>
> "PARENT_ID" text,
>
> "CODE" text,
>
> "DESCRIPTION" text,
>
> "SEQUENCE_NUMBER" integer,
>
> "ACCOUNT_GROUP_ID" integer,
>
> "FINANCIALREPORT_CATEGORY_ID" integer,
>
> "FINANCIALREPORT_DETAIL_ID" integer
>
> )
>
> AS
>
> $BODY$
>
> SELECT "CATEGORY_NODE_ID", "PARENT_ID", "CODE",
> "DESCRIPTION", "SEQUENCE_NUMBER",
>
> "ACCOUNT_GROUP_ID",
> "FINANCIALREPORT_CATEGORY_ID", "FINANCIALREPORT_DETAIL_ID"
>
> FROM _tbl_type;
>
> $BODY$
>
> LANGUAGE sql VOLATILE
>
> COST 100;
>
> ALTER FUNCTION "CategoryBulkImport"("CategoryType")
>
> OWNER TO postgres;
>
> Here I want to use table type parameter(_tbl_type) inside function with
> select statement but not able to access it so please suggest me a way.
>
> Here I am not sure about my function so please correct me if I am wrong.
CREATE OR REPLACE FUNCTION "CategoryBulkImport"("CategoryType")
RETURNS
TABLE (
"CATEGORY_NODE_ID" text,
"PARENT_ID" text,
"CODE" text,
"DESCRIPTION" text,
"SEQUENCE_NUMBER" integer,
"ACCOUNT_GROUP_ID" integer,
"FINANCIALREPORT_CATEGORY_ID" integer,
"FINANCIALREPORT_DETAIL_ID" integer
)
AS
$BODY$
SELECT $1.*;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
See here:
https://www.postgresql.org/docs/9.6/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
>
> Thank you.
>
> Best Regards,
>
> Kanji Kanzariya
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Function with table Valued Parameters execution issue
@ 2017-07-13 13:53 Kanjibhai.Kanzaria@thomsonreuters.com
0 siblings, 0 replies; 3+ messages in thread
From: Kanjibhai.Kanzaria@thomsonreuters.com @ 2017-07-13 13:53 UTC (permalink / raw)
To: pgsql-sql
Hello ,
Function with table Valued Parameters execution issue
I have created data type which replicates data table in our application and created one function with table valued parameter.
I want to call postgres function from my c# console application with NHibernate.
Any idea regarding that or can please suggest me how can call?
Best Regards,
Kanji
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-07-13 13:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-10 12:49 Function with table Valued Parameters execution issue Kanjibhai.Kanzaria@thomsonreuters.com
2017-07-10 13:26 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-07-13 13:53 Function with table Valued Parameters execution issue Kanjibhai.Kanzaria@thomsonreuters.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox