Converting SQL Server stored procedure to Postgres

Question

I am migrating my application from SQL Server to Postgres 11. I am not able to find any solution for migrating procedure.

ALTER PROCEDURE [dbo].[sp_DeleteAttachmentsForArtefacts] 
    @artefactTypeId BIGINT, 
    @artefactIds IdListTable READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DELETE tbl_Resources_Attachment 
    WHERE ContextTypeId = @artefactTypeId 
      AND ContextId IN (SELECT Id FROM @artefactIds)    
END

CREATE TYPE [dbo].[IdListTable] AS TABLE
(
  [Id] [BIGINT] NOT NULL,
  PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF)
)

I have created userdefined type in Postgres. But it is not working in "select Id from @artefactIds". It is giving error "artefactIds" doesn't exit.

Unrelated, but: dba.stackexchange.com/questions/154251/…

user330315
– user330315

2019-07-25 06:18:46 +00:00
Commented Jul 25, 2019 at 6:18 — user330315
– user330315, Commented Jul 25, 2019 at 6:18

user330315 · Accepted Answer · 2019-07-25 06:17:42Z

1

You don't need any special type for this, you can simply pass an array:

create or replace procedure sp_deleteattachmentsforartefacts(p_artefact_id integer, p_idlist integer[]) 
as
$$
begin

  delete from tbl_Resources_Attachment 
  where ContextTypeId = p_artefact_id 
    and ContextId = any(p_idlist);

end;
$$
language plpgsql;

To run it, call it like this:

call sp_DeleteAttachmentsForArtefacts(42, array[1,2,3,4]);

answered Jul 25, 2019 at 6:17

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Hardik Over a year ago

With this solution I am facing one problem now. I am not able to get below result in case of postgres. declare discussionIds IdListTable insert into discussionIds select Id from tbl_Notification_Discussion where ContextTypeId = artefactTypeId Here I can get multiple records from table and able to store it into userdefined datatype.

user330315 Over a year ago

@Hardik: please ask a new question. But it sounds as if you want a select array_agg(...) from ... to retrieve the IDs as an array. But the whole thing sounds rather strange. Why can't the delete procedure run that subselect? Pass huge lists of IDs around is a questionable design.

Ajay · Accepted Answer · 2019-07-25 06:11:15Z

0

Use the following function as Procedures are not fully added in PostgreSQl

DROP TABLE IF EXISTS source;
DROP FUNCTION IF EXISTS sp_DeleteAttachmentsForArtefacts(integer,IdListTable);
DROP TYPE IdListTable;

CREATE TYPE IdListTable AS (
    id INTEGER
   ,t  TIMESTAMP
   ,x  FLOAT
);



CREATE TABLE source OF IdListTable (PRIMARY KEY(Id));
INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0)
   ,(3, '2016-01-01 01:00:00', 12.0)
   ,(4, '2016-01-01 01:30:00',  9.0)
   ;


create or replace function sp_DeleteAttachmentsForArtefacts(artefactTypeId integer,_source IdListTable ) returns integer as
$$
begin

delete from tbl_Resources_Attachment where ContextTypeId = artefactTypeId and ContextId in (select Id from source);

return 1;

end
$$
language plpgsql;

answered Jul 25, 2019 at 6:11

Ajay

7744 silver badges13 bronze badges

1 Comment

Hardik Over a year ago

I don't want separate table to store records. "IdListTable" shoud be pass as parameter and I should be able to pass multiple records to this. Still I didn't get solution to pass multiple parameter. I can pass ROW(1, '2016-01-01 00:00:00', 10.0).

Collectives™ on Stack Overflow

Converting SQL Server stored procedure to Postgres

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related