Convert SQL Server stored procedure to PostgreSQL function

Question

I am new in PostgreSQL, I have a stored procedure which is written in T-SQL and I want to convert this to PostgreSQL. I have done so far but I am stuck at place I searched so many places but not found anything in internet.

Below is the T-SQL stored procedure which I need adapted to PGSQL

If <someCondi>
    BEGIN
        ...
    END
ELSE
    BEGIN
        GENERATE:
            BEGIN try
                IF <someCondi>
                    BEGIN
                        ...
                    END
                ELSE
                    BEGIN
                        ...
                    END
            END try
            BEGIN catch
                IF <someCondi>
                    BEGIN
                        ...
                    END
                if @LoopCount<=2
                begin
                    goto GENERATE    
                end
            END catch
    END

I have convert most of the stored procedure part but I need alternate of else part. Don't know how to convert try catch in pgsql and also what is alternative for GOTO. Please help

"Don't know how to convert try catch in pgsq" - Trapping Errors in the PostgreSQL manual — user330315
– user330315, Commented Sep 2, 2020 at 14:55
@a_horse_with_no_name i saw that Trapping error but problem is that don't know how to use that in this situation — Dexter
– Dexter, Commented Sep 2, 2020 at 14:58
You can't convert code from one language or dialect to another if you don't know both of them. In this case the syntax is quite similar - instead of BEGIN TRY, just BEGIN. Instead of BEGIN CATCH, EXCEPTION — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 2, 2020 at 15:11

Belayer · Accepted Answer · 2020-09-02 19:04:11Z

While plpgsql is a block structures language it does not require Begin...End on each if condition nor on the else. It does however require the THEN key word after If condition and End if to terminate.
As already mentioned there if no "Try ... Catch" structure. Basically every statement is implicitly a "try statement" as any statement may throw an exception. The catch portion is supplied by the EXCEPTION section at the end of the block. Any error in the entire block will be caught here. I suggest you spend some time with the the documentation. The following would seem to be the structure you need.

declare                       -- assumed as not present in post                                                    
   loopcount integer := 1;    -- initial value my assumption                                                       
begin                         -- procedure (outer block)                                                           
    while loopcount <= 2      -- generate                                                                          
    loop                                                                                                           
                                                                                                                   
       begin                  -- nested block                                                                      
          if <somecondi>                                                                                           
          then                                                                                                     
             ...                                                                                                   
          else                                                                                                     
              if <somecondi>                                                                                       
              then                                                                                                 
                 ...                                                                                               
               else                                                                                                
                 ...                                                                                               
              end if;                                                                                              
          end if ;                                                                                                 
                                                                                                                   
       exception              -- catch                                                                             
          when others then                                                                                         
               if <somecondi>                                                                                      
               then                                                                                                
                ...                                                                                                
               end if;                                                                                             
       end;                   -- nested block                                                                      
                                                                                                                   
    end loop;                 --  generate                                                                         
end ;                         --  procedure (outer block)

Collectives™ on Stack Overflow

Convert SQL Server stored procedure to PostgreSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related