Table valued Parameter Equivalent in Postgresql

Question

In postgresql- what is the equivalent of stored procedure with table valued paramater(MSSQL)?

Answer 1

@HamidKhan -Is your development language Java or C #?

CREATE TABLE public.employee (
  emp_id INTEGER NOT NULL,
  emp_nm VARCHAR(40),
  first_in_time TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp(),
  last_chg_time TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp(),
  CONSTRAINT employee_pkey PRIMARY KEY(emp_id)
)
WITH (oids = false);

CREATE TYPE public.employee_udt AS (
  emp_id INTEGER,
  emp_nm VARCHAR(40)
);

--c# code 1

public class EmployeeUdt
{
    [PgName("emp_id")] 
    public int EmpId { get; set; } 

    [PgName("emp_nm")]
    public string EmpNm { get; set; }
}

--c# code 2

List<EmployeeUdt> lst_param = new List<EmployeeUdt>();

for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
lst_param.Add(
new EmployeeUdt
{
    EmpId = Convert.ToInt32(this.dataGridView1[0, i].Value),
    EmpNm = this.dataGridView1[1, i].Value.ToString()
}
);
}

var _param = new[] {
new NpgsqlParameter
{
    ParameterName="p_employee",
    NpgsqlDbType = NpgsqlDbType.Composite,
    SpecificType = typeof(EmployeeUdt[]),
    NpgsqlValue = lst_param
}

};

SqlHelper.ExecuteNonQuery<EmployeeUdt>(this.connstring, "usp_set_emp", _param);

Answer 2

PostgreSQL has no table variables. The most similar type is a composite array:

create type foo_typ as (a int, b int);

do $$
declare
  f foo_typ[];
  r foo_typ;
begin
  f := ARRAY(SELECT row(10, 20)::foo_typ from generate_series(1,10));
  for r in select * from unnest(f) g(v)
  loop
    raise notice 'a:%, b:%', r.a, r.b;
  end loop;
end;
$$;
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
NOTICE:  a:10, b:20
DO

Writing stored procedures in PostgreSQL, you are practically starting from zero coming from T-SQL in terms of syntax familiarity. The PostgreSQL syntax is similar to Oracle and DB2 - but very different to SQL Server.

Answer 3

You can use a JSON array as an input with jsonb_to_recordset(). That's the easiest way I've found. Just make a JSON array filled with objects. I'm not sure it's as fast as a true table value parameter in SQL Server but it only requires one request to insert or update many values.

This is an example from my code base using Dapper. Notice how I convert the array to a JSON string. Then it uses jsonb_to_recordset to conver that to a table.

await cnTeam.ExecuteAsync(@"
update enterprisecontact
set
primarysource_contactid = x.primarysource_contactid,
primarysource_contact_recordtype = x.primarysource_contact_recordtype,
primarysource_accountid = x.primarysource_accountid,
primarysource_accountname = x.primarysource_accountname,
primarysource_createdate = x.primarysource_createdate,
primarysource_updatedate = x.primarysource_updatedate
from (
    select * from jsonb_to_recordset(:jsoninput::jsonb) as y(
        emailaddress text, 
        primarysource_contactid text,
        primarysource_contact_recordtype text,
        primarysource_accountid text,
        primarysource_accountname text,
        primarysource_createdate timestamp with time zone,
        primarysource_updatedate timestamp with time zone
        )
) x
where enterprisecontact.emailaddress = x.emailaddress
", new { jsoninput = JsonConvert.SerializeObject(chunk) });