Getting Err in Postgres Stored Procedure / Function

Question

CREATE OR REPLACE function spInsertReleaseSheet (
p_RedlineNo int,
p_RedlineDesc varchar(500),
p_ParentRedlineId int,
p_CategoryId int,
p_ChangeTypeId int,
p_ObjectName varchar(500),
p_DeployInfo int,
p_Notes varchar(500),
p_CodeReview int,
p_Env int,
p_TechId varchar(500),
p_CreatedBy varchar(50),
p_DeployStatus varchar(50),
p_ReleaseCodeStatus int) 

RETURNS VOID as $$

DECLARE v_redlineid int;
DECLARE v_objectid int;
Begin


if(COALESCE(p_CreatedBy,'')='')
then
    p_CreatedBy:='Suresh';
end if;

if(COALESCE(p_DeployStatus,'')='')
then
    p_DeployStatus:='TBD';
end if;
BEGIN
--LOOP
    START TRANSACTION;
    IF EXISTS (SELECT  "RedlineNumber" from public."Redline"  where RedlineNumber=p_RedlineNo LIMIT 1)
            Then
                select  "RedlineId" into v_redlineid from public."Redline" where RedlineNumber=p_RedlineNo LIMIT 1;
            else
                insert into Redline(RedlineNumber,ParentRedlineId,RedlineDesc,CreatedDate,CreatedBy)
                Select p_RedlineNo,p_ParentRedlineId,p_RedlineDesc,now(),p_CreatedBy

             RETURNING Redline into  v_redlineid;
            end if;

        insert into Objects(CategoryId,Name) 
        Select p_CategoryId,p_ObjectName
        RETURNING Redline into v_objectid;


        insert into ObjectImplementation(RedlineId,CodeReviewStatusId,ChangeTypeId,Instructions,ObjectId,Notes,DeploymentStatus,EnvironmentId,TechReqId)
        Select v_redlineid,p_CodeReview,p_ChangeTypeId,p_DeployInfo,v_objectid,p_Notes,p_DeployStatus,p_Env,p_TechId;

        insert into ObjectImplementationHistory(ReleaseCodeStatusTypeId,CreateDatetime,EnvironmentId,ObjectId,RedlineId)
        Select p_ReleaseCodeStatus,now(),p_Env,v_objectid,v_redlineid;

        COMMIT;
        --END LOOP
        EXCEPTION
         WHEN OTHERS THEN
              -- if error, roll back any chanegs done by any of the sql statements
           ROLLBACK;
END;
$$ LANGUAGE plpgsql;

================================================

I am getting a error

ERROR:  syntax error at end of input
LINE 64: $$ LANGUAGE plpgsql;
         ^
SQL state: 42601
Character: 1750

Can some resolve this issue Function

When I run the function I am getting the Error Below

=================================================
ERROR:  function public.spinsertreleasesheet(integer, unknown, unknown, integer, integer, unknown, integer, unknown, integer, integer, unknown, unknown, integer, integer) does not exist
LINE 1: SELECT public.spinsertreleasesheet(
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8
=======================================
SELECT public.spinsertreleasesheet(
    177771, 
    '177771 : Tests', 
    NULL, 
    1, 
    1, 
    'Test', 
    1, 
    NULL, 
    1, 
    1, 
    NULL, 
    'Test', 
    1, 
    1
)

Eventhough the function is there in DB it is giving error

looks like another "END;" is missing after ROLLBACK; in exception — JosMac
– JosMac, Commented Mar 15, 2019 at 13:47
you have two begin and only one end fix it with your logic. — user8060120
– user8060120, Commented Mar 15, 2019 at 13:48

404 · Accepted Answer · 2019-03-15 13:48:07Z

1

You've got 2 BEGINs and only one END. You also have two DECLARE sections for no reason. Change to: one DECLARE, and either put a second END or use only one BEGIN.

answered Mar 15, 2019 at 13:48

404

8,7522 gold badges33 silver badges53 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

Also, the START TRANSACTION is not going to work.

Collectives™ on Stack Overflow

Getting Err in Postgres Stored Procedure / Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related