Using DELETE FROM...RETURNING in a PostgreSQL IF / THEN Statement

Question

I have this code:

DO $$
returns table (msg varchar(500), isSuccessful BIT) as $BODY$
declare
newID integer := null;
id integer := 100;
BEGIN
IF newID is NULL 
then delete from table1 t1 where t1.id = id;
delete from table2 t2 where t2.id = id
returning 'test' as msg, 1 as isSuccessful;
else insert into table1(id, name)
values(id, 'testname');
END IF;
END $$;

When I run this, I'm getting this error:

ERROR: syntax error at or near "returns"

I originally didn't have the returns table line, but after some research it's my understanding that I need to establish a table for the data in the returning line to write into.

What I want to return is the following:

msg	isSuccessful
test	1

What am I doing wrong with my returns table line and how do I achieve the output I'm looking for? Also, do I have to create a function in order for this to work?

Your function is expection an array as result. what happens IF newID is not NULL . You arent returning anything ! — DEV
– DEV, Commented Oct 22, 2021 at 19:15
@Hamza Ok updated my code to insert into table1 for the ELSE. Still getting the same error for my RETURNS TABLE line. — cp1113
– cp1113, Commented Oct 22, 2021 at 20:10
You can't return anything from a DO(anonymous) function. See DO. — Adrian Klaver
– Adrian Klaver, Commented Oct 22, 2021 at 20:13
@AdrianKlaver My understanding of functions is limited, but I understand your comment. Is there a way to achieve my desired output without defining a function at all? — cp1113
– cp1113, Commented Oct 22, 2021 at 20:24
Depends what you want to do with info. If you just want a message the RAISE NOTICE 'msg: %, isSuccessful: %', msg, isSuccessful;. See here Raising messages/errors. Otherwise you will have to write it out to a table. FYI, Postgres has boolean so I would use that rather then BIT. I would spend some time here plpgsql — Adrian Klaver
– Adrian Klaver, Commented Oct 22, 2021 at 20:45

Ramin Faracov · Accepted Answer · 2021-11-01 22:43:31Z

Example how you can insert deleted records from table sample1 into table sample2:

with deleted_rows as (
    delete from test.sample1 where id = 123712
    returning id, first_name 
)
insert into test.sample2 (id, first_name) 
SELECT id, first_name from deleted_rows;

But, if you want to get table on this format (msg, isSuccessful) then:

CREATE OR REPLACE FUNCTION test.delete_data(p_id integer)
RETURNS table (msg text, isSuccessful boolean)
LANGUAGE plpgsql
AS $function$
declare 
    deleted_id integer; 
begin

    delete from test.sample1 where id = p_id
    returning id into deleted_id;
    -- if this query does not delete any record, so variable "deleted_id" will be null

    if (deleted_id is not null) then 
        return query 
        select 'Success Deleted', true;
    else 
        return query 
        select 'No records deleted', false;
    end if;

end;
$function$
;

Collectives™ on Stack Overflow

Using DELETE FROM...RETURNING in a PostgreSQL IF / THEN Statement

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