postgresql code for delete operation in table type

Question

I have a code for deleting table type in MSSQL.Have to convert into PGSQL which is giving error. Looking forward for a PGSQL code which deletes from a Table Type User defined: below MSSQL CODE:

declare @Entity TRef_StructureTree readonly //input parameter from procedure
DELETE Tef_StructureTree 
FROM Tef_StructureTree
inner join (select * from @Entity) as source
on Tef_StructureTree.ChildCodeBDR=source.ChildCodeBDR AND 
Tef_StructureTree.ChildScheme=source.ChildScheme;

Below is definition of USer defined Table type:

CREATE TYPE [dbo].[Tef_StructureTree] AS TABLE(
    [ChildCodeBDR] [numeric](10, 0) NOT NULL,
    [ChildScheme] [nvarchar](100) NOT NULL,
    )

below PGSQL CODE:

CREATE OR REPLACE FUNCTION UpdateStrucTree(v_entity Tef_StructureTree[])
 as 
begin
    DELETE FROM v_entity   
        where v_entity."ChildCodeBDR" in(select "source"."ChildCodeBDR" from  unnest(v_entity)  as "source" )
        and v_entity."ChildScheme" in (select "source"."ChildScheme"  from  unnest(v_entity)  as "source" );
 end;

ERROR: relation "v_entity" does not exist Please Help by providing the equivalent!!

Answer 1

Your parameter is called v_entitydata not v_entity, so you either need to change your parameter name or the reference to it. You probably want to delete from the table Tef_StructureTree not from the passed array.

Your function's structure is also not valid for Postgres as the function body needs to be provided as a string, e.g. using dollar quoting.

You can simplify the condition, by only using a single sub-query as well.

So putting all that together, the function should look like this:

CREATE OR REPLACE FUNCTION UpdateStrucTree(v_entity "Tef_StructureTree"[])
as 
$$
  DELETE FROM "Tef_StructureTree"
  where ("ChildCodeBDR", "ChildScheme") in (select "ChildCodeBDR", "ChildScheme"
                                            from unnest(v_entity) )
$$
language sql;