Subject: Re: Function with table Valued Parameters execution issue
To: Kanjibhai.Kanzaria@thomsonreuters.com, pgsql-sql@postgresql.org
References: 
 <58C4738291E8114E98343B342437EF54048F363C@HYDR-ERFMMBS21.ERF.thomson.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <b2057259-b7d5-f61d-82a0-cf97f0f73ebe@aklaver.com>
Date: Mon, 10 Jul 2017 06:26:55 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
In-Reply-To: 
 <58C4738291E8114E98343B342437EF54048F363C@HYDR-ERFMMBS21.ERF.thomson.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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