Postgres sql function is not giving proper count

Question

I am using below query to check count of data in db below is code.

--- select "fn_delete_data"('7503','Test','ONE');
-- DROP FUNCTION fn_delete_data(character varying,character varying,character varying) 
CREATE OR REPLACE FUNCTION public."fn_delete_data"(
    IN IN_id character varying,
    IN IN_source character varying,
    IN IN_deletion_type character varying,
    OUT Out_message character varying,
    OUT Out_is_success INTEGER
)AS $$
DECLARE
    v_count integer := 0;
BEGIN
raise notice 'v_count %', v_count;
    CREATE TEMP TABLE "simple_count" ON COMMIT DROP AS(select * from public.client where "other_id" = IN_id
                UNION
                select * from public.client where "id" = IN_id);
                
    select count(*) INTO v_count from simple_count; 
    raise notice 'v_count %', v_count;
    IF (v_count > 0) THEN
        Out_is_success = 0;
        Out_message = 'Data Found. Not Allow to delete';
    ELSE
        Out_is_success = 1;
        Out_message = 'No Data found. Allow to delete';
    END IF;
END 
$$ LANGUAGE plpgsql;

I have print v_count which is giving every time diff value. first print giving me v_count 0 another print giving every time diff count when i run query outside function it giving me proper 1 count. I don't know what i am doing wrong please help.

What is the issue you are trying to resolve? Your function does not make any since; it basically says "if the data does not exist tell the caller it is OK to delete it". Please add a description in the business issue you face, not what is wrong with an invalid function. — Belayer
– Belayer, Commented Mar 14, 2021 at 21:19

eshirvana · Accepted Answer · 2021-03-14 18:46:20Z

1

I think problems is on your temp table ON COMMIT DROP, I think it drops itself after creating itself because there is no transaction, so you can simplify your function to this and it should work:

CREATE OR REPLACE FUNCTION public."fn_delete_data"(
    IN IN_id character varying,
    IN IN_source character varying,
    IN IN_deletion_type character varying,
    OUT Out_message character varying,
    OUT Out_is_success INTEGER
)AS $$
DECLARE
    v_count integer := 0;
BEGIN
    raise notice 'v_count %', v_count;
    select count(*) INTO v_count from public.client where IN_id in ("Id", "other_id"); 
    raise notice 'v_count %', v_count;
    IF (v_count > 0) THEN
        Out_is_success = 0;
        Out_message = 'Data Found. Not Allow to delete';
    ELSE
        Out_is_success = 1;
        Out_message = 'No Data found. Allow to delete';
    END IF;
END 
$$ LANGUAGE plpgsql;

edited Mar 14, 2021 at 18:46

answered Mar 14, 2021 at 18:34

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

Postgres sql function is not giving proper count

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