vendredi 22 octobre 2021

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

Aucun commentaire:

Enregistrer un commentaire