PostgreSQL variable in select and delete statements

Question

The Problem: I have many delete lines in a PostgreSQL script where I am deleting data related to the same item in the database. Example:

delete from <table> where <column>=180;
delete from <anothertable> where <column>=180;
...
delete from <table> where <column>=180;
commit work;

There are about 15 delete statements deleting data that references <column>=180.

I have tried to replace the 180 with a variable so that I only have to change the variable, instead of all the lines in the code (like any good programmer would do). I can't seem to figure out how to do it, and it's not working.

NOTE: I am very much a SQL novice (I rarely use it), so I know there's probably a better way to do this, but please enlighten me on how I can fix this problem.

I have used these answers to try and fix it with no luck: first second third. I've even gone to the official PostgreSQL documentation, with no luck.

This is what I'm trying (these lines are just for testing and not in the actual script):

DO $$
DECLARE
    variable INTEGER:
BEGIN
    variable := 101;
    SELECT * FROM <table> WHERE <column> = variable;
END $$;

I've also tried just delcaring it like this:

DECLARE variable INTEGER := 101;

Whenever I run the script after replacing one of the numbers with a variable this is the error I get:

SQL Error [42601]: ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function inline_code_block line 6 at SQL statement

Can someone tell me where I'm going wrong? It would be nice to only have to change the number in the variable, instead of in all the lines in the script, and I just can't seem to figure it out.

in plpgsql you can select into variable, but can't just select, as then it has no destination for result, so either use `perform` or use select result as implicit variable assingment, eg `raise info '%',(select * from where = variable);` (which again wont work if returns more then one row... — Vao Tsun, Nov 01 '17 at 18:03

Michel Milezzi · Accepted Answer · 2017-11-01T18:56:01.463

As @Vao Tsun said, you must define a destination to your SELECT statement. Use PERFORM otherwise:

--Test data
CREATE TEMP TABLE my_table (id, description) AS 
    VALUES (1, 'test 1'), (2, 'test 2'), (101, 'test 101');

--Example procedure
CREATE OR REPLACE FUNCTION my_procedure(my_arg my_table) RETURNS VOID AS $$
BEGIN
    RAISE INFO 'Procedure: %,%', my_arg.id, my_arg.description;
END
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    variable INTEGER;
    my_record my_table%rowtype;
BEGIN
    variable := 101;

    --Use your SELECT inside a LOOP to work with result   
    FOR my_record IN SELECT * FROM my_table WHERE id = variable LOOP
        RAISE INFO 'Loop: %,%', my_record.id, my_record.description;
    END LOOP;

    --Use SELECT to populate a variable. 
    --In this case you MUST define a destination to your result data
    SELECT * INTO STRICT my_record FROM my_table WHERE id = variable;   
    RAISE INFO 'Select: %,%', my_record.id, my_record.description;

    --Use PERFORM instead of SELECT if you want to discard result data
    --It's often used to call a procedure 
    PERFORM my_procedure(t) FROM my_table AS t WHERE id = variable;

END $$;

--DROP FUNCTION my_procedure(my_table);

PostgreSQL variable in select and delete statements

1 Answers1