Table valued function parameter in postgres SQL

   In Postgres -SQL server whenever we create a new table it automatically creates a composite Type that corresponds with structure of table. we can use this type to create a variable ,it can hold a single row of corresponding table.

In situation where we need to pass whole result-set as parameter to Postgres function there seems to be no native support in postgres unlike Microsoft SQL server so converting a existing MSSQL Procedure to Postgres function become quite a headache.

we can create an equivalent representation of result set in XML or JSON and pass it to function; inside function we can deserialize this into result-set for purpose of further operation on result-set that way we can compensate absence of Table Valued Parameters in Postgres SQL .

 Along with JSON & XML we can also create an array of objects from result-set to function as parameter.

Here I will demonstrate this approach with inbuilt Array functionality of Postgres.

Lets create a Table as follows

CREATE TABLE public."Projects"

(

   id integer NOT NULL,

  "createdAt" timestamp with time zone,

  "updatedAt" timestamp with time zone,

  name character varying(255) NOT NULL,

  "UserId" integer

);

For purpose of demonstration add few records into “Projects” table with two different user ids say 1 & 2.

Now let’s create a one more table that will used for passing result-set to our function, function is trivial one it just give count of rows user wise

CREATE table projectlist

(

                list int[],

                user_id int

);

Now we will use ROW construct to serialize our result-set and save that as a single row in projectlist table as follows

insert into projectlist(user_id,list)

select  1 "UserId",ARRAY(select id from "Projects" where "UserId" =1) ;

insert into projectlist(user_id,list)

select  2 "UserId",ARRAY(select id from "Projects" where "UserId" =2) ;

The Array function will convert values passed to it as an array e.g if it got 3 records as input  14,15,16 then resultant value will {14,15,16}. unnest is another Postgres function that do exactly reverse of it that we will use for de-serialization.

Below is my function that will read the record set

CREATE OR REPLACE FUNCTION public.demo_func(

    IN  list1 projectlist,

    IN  list2 projectlist)

  RETURNS TABLE(user_id int, count bigint) AS

$BODY$

BEGIN

               

        RETURN QUERY

                WITH  list1_cte AS (

                              

                ),

                list2_cte  AS (

                              

                )

                select  list1_cte.user_id,count(*) as count from list1_cte group by list1_cte.user_id

                union

                select  list2_cte.user_id,count(*) as count from list2_cte group by list2_cte.user_id;

END

$BODY$

LANGUAGE plpgsql ;

Now we need to join all dots and call our function by passing serialized result set.We create two table type variables list1 & list2 and give them serialized data that we had already inserted into our table created for same purpose.

   After that we will call our function that will return some records that I am looping using cursor.

DO

$$

DECLARE list1 projectlist;

DECLARE list2 projectlist;

DECLARE cursor_op_record RECORD;

DECLARE cursor_var refcursor;

BEGIN

                select p.* into list1  from projectlist p where user_id=1;

                select q.* into list2  from  projectlist q where user_id=2;

   OPEN cursor_var FOR

   select user_id,count from public.demo_func(list1,list2);

   RAISE INFO 'user_id, count';

   LOOP

                FETCH cursor_var INTO cursor_op_record;

                EXIT WHEN NOT FOUND;

        RAISE INFO '%,%', cursor_op_record.user_id, cursor_op_record.count;

   END LOOP;

END$$;

Here  our table projectlist has list column which is just an int[] but we can also replace it with array of composite type object and pass a single tuple.