declare variable for a postgresql query using like and union-CodePudding

I want to do this:

select * from table_1 where table_1.column1 ilike '%value%' union
select * from table_1 where table_1.column2 ilike '%value%' union
select * from table_1 where table_1.column3 ilike '%value%';

but use a single variable for '%value%', something like this:

do $$
    declare
        my_var TEXT;
    begin
        my_var = '%value%';
        select * from table_1 where table_1.column1 ilike my_var union
        select * from table_1 where table_1.column2 ilike my_var union
        select * from table_1 where table_1.column3 ilike my_var;
end $$;

but it doesn't seem to work (I'm kind of new to this) and I can't find the solution to do this. It doesn't have to be a do/end statement. I'm just trying to declare a variable to use more than once in the query, so that I don't have to copy and paste '%value%' 3 times every time I want to change it (keep in mind this %value% will always be the same; hence why I want it to be in my_var). Just want to change it once for all three statements and print out the same details.

CodePudding user response：

https://www.postgresql.org/docs/current/sql-do.html

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

The name of the procedural language the code is written in. If omitted, the default is plpgsql.

PL/pgsql reference: https://www.postgresql.org/docs/current/plpgsql.html

Since do command code block is returning void, we can use raise notice to extract/debug what we do. To print out something from console, generally we need some variable to hold what we want to compute/final result. The following is an simple example to count the return query rows.

CREATE temp TABLE a1 (
    column1 text,
    column2 text,
    column3 text
);

INSERT INTO a1
    VALUES ('value', 'test', 'test1');

INSERT INTO a1
    VALUES ('misc', 'value2', 'test2');

INSERT INTO a1
    VALUES ('misc1', 'test3', 'value3');

DO $$
DECLARE
    my_var text;
    _count bigint;
BEGIN
    my_var = '%value%';
    SELECT
        count(*) INTO _count
    FROM (
        SELECT * FROM a1 WHERE column1 ILIKE my_var
        UNION
        SELECT * FROM a1 WHERE column2 ILIKE my_var
        UNION
        SELECT * FROM a1 WHERE column3 ILIKE my_var
        ) cte;
    RAISE NOTICE '_count = %', _count;
END
$$
LANGUAGE plpgsql;