Home How to create a table variable in a procedure that inherits a type that has already been created previously on postgresql?

Questions

How to create a table variable in a procedure that inherits a type that has already been created previously on postgresql?

byMR

January 20, 2023

I am doing a migration exercise from SQL Server to Postgres and I found this type of variable in a stored procedure:

CREATE     PROCEDURE [dbo].[CambiodedepositanteTIDIS_CERTS]
@Client(50),
@Email varchar(50),
@Planilla1 [dbo].[TipoPlanilla1]    Readonly

In the variable planila1 , the type TipoPanilla1 is being inherited, if I understood correctly, this is the type:

CREATE TYPE [dbo].[TipoPlanilla1] AS TABLE(
    [CuentaDeposito] [bigint] NULL,
    [CodigoOyd] [decimal](18, 0) NULL,
    [Especie] [varchar](150) NULL,
    [Isin] [varchar](50) NULL,
    [Emision] [decimal](18, 0) NULL,
    [ValorTrasladar] [decimal](18, 0) NULL
)

Is there a way to do something similar in postgress?

>Solution :

Postgres has no "table variables", but you could pass an array of a type.

CREATE TYPE tipoplanilla1 AS 
(
  cuentadeposito bigint,
  codigooyd decimal(18, 0),
  especie varchar(150),
  isin varchar(50),
  emision decimal(18, 0),
  valortrasladar decimal(18, 0)
);

Then declare a parameter as an array:

create procedure cambiodedepositantetidis_certs(
    client text, email text, planilla1 tipoplanilla1[])
as
$$
begin
....
end
$$
language plpgsql;

Note that for every table that is created, Postgres automatically creates a type with the same name. So if you want to pass "rows" of a table, there is no need to create an extra type. You can use the table’s name as the parameter type.