0

I have created one function in postgresql. but when i try to return data i am getting below error

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "fn_GetAllCountData"() line 27 at SQL statement
SQL state: 42601

Below is the my postgresql function. In this function I am getting task status count in one query

CREATE OR REPLACE FUNCTION public."fn_GetAllCountData"() RETURNS setof "AssignDetails"  AS $BODY$
DECLARE
    total_draft text;
    total_pending text;
    total_rejected text;
    total_approved text;
    total_prev_pending text;
    "AssignDetails" text;
BEGIN

    --Total pending application no by the user
    Select k."UserCode" as "UserCode",count(S."taskAssignTo") as "TotalPending" into total_pending 
    from user
    left Outer Join public."tbl_task" S
    on k."UserCode"=S."taskAssignTo" and s.Status='P'
    And to_char(S."assignDate"::date, 'dd-mm-yyyy') = to_char(current_Date, 'dd-mm-yyyy') 
    group by k."UserCode";

    --Previous Pending
    Select k."UserCode" as "UserCode",count(S."taskAssignTo") as "TotalPrevPending" into total_prev_pending 
    from kyc k
    left Outer Join public."tbl_task" S
    on k."UserCode"=S."taskAssignTo" and s.Status='P'
    And S."assignDate" < CONCAT(current_Date, ' 00:00:00'):: timestamp
    group by k."UserCode";

    -- Total Objection raised by the user 
    Select k."UserCode" as "UserCode",count(S."taskAssignTo") as "TotalRejected" into total_rejected 
    from kyc k
    left Outer Join tbl_task S 
    on k."UserCode"=S."taskAssignTo" and s.Status='R'
    And to_char(S."objectionDate"::date, 'dd-mm-yyyy') = to_char(current_Date, 'dd-mm-yyyy') 
    group by k."UserCode";

    -- Total Approved application no by the user  
    Select k."UserCode" as "UserCode",count(S."taskAssignTo") as "TotalApproved" into total_approved 
    from kyc k
    left Outer Join public."tbl_task" S
    on k."UserCode"=S."taskAssignTo" and s.Status='A'
    And S."assignDate" < CONCAT(current_Date, ' 00:00:00'):: timestamp
    group by k."UserCode";

    --Application no with start Time and total time
    Select K."UserCode",K."Status", K."AppType",ST."taskNo" as "TaskId", ST."startTime" as "StartTime",    
    case 
        when COALESCE(ST."endTime",'')='' 
        then (SELECT DATEDIFF('second', ST."startTime":: timestamp, current_timestamp::timestamp))
        else (SELECT DATEDIFF('second', ST."startTime":: timestamp, ST."endTime"::timestamp))
        end as "Totaltime"
    into "Final" 
    from kyc K      
    left outer join public."tbl_task_details" ST 
    On K."UserCode"=ST."empCode";

    --Total Checked In Draft application no through by the user
    Select k."UserCode" as "UserCode",count(S."taskAssignTo") as "Status_Count" into total_draft 
    from kyc k
    left Outer Join public."tbl_task" S
    on k."UserCode"=S."taskAssignTo" and s.Status='D'
    And S."assignDate" < CONCAT(current_Date, ' 00:00:00'):: timestamp
    group by k."UserCode";

    Select distinct K."UserCode",K."Status",K."AppType",K."LoginTime",K."LogoutTime",
    F."TaskId",F."StartTime",F."Totaltime",  
    TP."TotalPending" as "Pending",  
    TR."TotalRejected" as "Objection",  
    TA."TotalApproved" as "Approved",  
    TS."TotalAssign" as "Total_Assigned",    
    TD."Status_Count" as "Draft_Count",
    TPP."TotalPrevPending" As "Prev_Pending"  
    into "AssignDetails"
    From "Final" F
    Right outer join kyc K  On K."UserCode"=F."UserCode"
    left outer join total_scrutiny TS On K."UserCode"=Ts."UserCode"        
    left outer join total_draft TD On TD."UserCode"=K."UserCode"  
    left outer join total_pending TP On TP."UserCode"=K."UserCode"
    left outer join total_rejected TR On TR."UserCode"=K."UserCode"   
    left outer join total_approved TA On TA."UserCode"=K."UserCode"
    Left Outer Join total_prev_pending TPP  On TPP."UserCode"=K."UserCode"
    order by TS."TotalAssign" desc;

    
    Select * From "AssignDetails";
END 
$BODY$ LANGUAGE plpgsql;

I tried to return table with return query but still not working. I don't know what i am doing wrong. Please help me with this.

Improve this question

1 Answer 1

Reset to default
2

Please note that postgreSQL only reports one error at a time. In fact there is a very great deal wrong with your function, so much so that it would take too long to correct everything here.

I have therefore given you a cut-down version here, which should point you in the right direction. I will give the code first, and then explain the points.

CREATE OR REPLACE FUNCTION public.fn_getallcountdata() RETURNS TABLE (usercode text, totalpending integer)  AS $BODY$
BEGIN

    CREATE TEMP TABLE total_pending
    (
        usercode text,
        totalpending int
    ) ON COMMIT DROP;
    
    --Total pending application no by the user
    INSERT INTO total_pending
    Select k.usercode, count(s.taskassignto)::integer 
    from public.user k
    left Outer Join public.tbl_task s
    on k.usercode=s.taskassignto and s.status='P'
    And s.assigndate::date = current_date 
    group by k.usercode;

    RETURN QUERY    
    select t.usercode, t.totalpending From total_pending t;
END; 
$BODY$ LANGUAGE plpgsql;

Points to note:

Firstly please avoid using mixed case names in postgreSQL. It means that you have to double quote everything which is a real pain!

Secondly, you were declaring variables as text, when in fact they were holding table data. This you cannot do (you can only put a single value in any variable). Instead you need to create temporary tables in the way I have done. Note in particular the use of ON COMMIT DROP. This is a useful way in postgreSQL to avoid having to remember to drop temporary tables when you are finished with them!

Thirdly your alias k is not referring to anything in your first select. Note also that user is a reserved word. If you insist on having user as a name for a table, then you will need to access it through public.user (assuming it is in the public schema).

(As an aside it is generally considered to be a security risk to use the public schema, because of guest access).

Fourthly there is no need to convert a date to string form in order to compare it. Casting a timestamp to a date and directly comparing to another date is in fact far faster, than converting both dates to a string representation and comparing the strings.

Fifthly COUNT in postgreSQL returns a bigint, which is why I generally cast it as integer, because an integer usually suffices!

I have defined the function to return a table containing named columns. You can use setof, but if you do it has to be a known table type.

For the final SELECT I have supplied the required RETURN QUERY first. Note also that I am using a table alias. This is because the column names in the returning table match those in the temporary table, so you need to be explicit as to what you are doing.

I strongly recommend that you experiment with a shorter function first, (as in my cutdown version) and then increase the complexity once you have it compiling (and running). To this end please also note that in postgreSQL, if a function compiles, it does not mean that it contains no runtime errors. Also if you change the return columns between different compilations, you will need to delete the previous version.

Hope this points you in the right direction, but please feel free to get back with any further issues.

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

5 Comments

Yes...got you point...working on changes...will let u know if any issue found
I am going to watch a football match soon, so it might take a while to respond, if you have follow-up questions, but I promise I will do, if no one else does in the meantime :-)
Just wanted to know what if i have return multiple select statement any idea??? like this RETURN QUERY select t.usercode, t.totalpending From total_pending t; select t.usercode, t.approve From total_approve t;
In PostgreSQL you cannot have multiple returning select statements. But in a case like yours, you can achieve what you want by joining them all on usercode, and having separate columns for totalpending and approve. In such cases it is often necessary to have a separate temporary table (or cte) which holds all the distinct usercodes, and then every other table is joined to this by an outer join.
"you cannot have multiple returning select statements" Thax for this...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.