Re: [SQL] Function with table Valued Parameters execution issue
От | Adrian Klaver |
---|---|
Тема | Re: [SQL] Function with table Valued Parameters execution issue |
Дата | |
Msg-id | b2057259-b7d5-f61d-82a0-cf97f0f73ebe@aklaver.com обсуждение исходный текст |
Ответ на | [SQL] Function with table Valued Parameters execution issue (<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
В списке pgsql-sql по дате отправления: