CODEWITHSUNDEEP
sqlpostgresqlfunctionif-statementsql-delete
cp1113
cp1113

Reputation: 47

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

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?

Upvotes: 4

Views: 11169

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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$
;

Upvotes: 4

Related Questions