From: | <Kanjibhai(dot)Kanzaria(at)thomsonreuters(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Function with table Valued Parameters execution issue |
Date: | 2017-07-10 12:49:06 |
Message-ID: | 58C4738291E8114E98343B342437EF54048F363C@HYDR-ERFMMBS21.ERF.thomson.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-07-10 13:26:55 | Re: Function with table Valued Parameters execution issue |
Previous Message | Tom Lane | 2017-07-06 19:43:23 | Re: Finding the negative |