declare variable for a postgresql query using like and union

Question

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.

What's your definition of " it doesn't seem to work" and don't forget this construction will never return anything: It's a DO block. "The code block is treated as though it were the body of a function with no parameters, returning void" — Frank Heikens
– Frank Heikens, Commented Aug 12, 2022 at 16:10
What is the point? You are actually writing more code and (imho) less clear code for what you are after and getting less value from it, — Belayer
– Belayer, Commented Aug 12, 2022 at 18:45
I essentially just want to do the same 3 select queries to print out all the rows without repeat using the same variable for the ilike values. I don't want to copy and paste '%someValue%' 3 different times. Just want to change one value for all 3 select statements. — H.Kim
– H.Kim, Commented Aug 12, 2022 at 19:31

jian · Accepted Answer · 2022-08-12 16:41:07Z

1

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;

answered Aug 12, 2022 at 16:41

jian

5,0231 gold badge29 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

H.Kim Over a year ago

Is there a way to see the rows instead of just the count? Just like the "select * union" above it? I essentially just want to do the same query but I don't want to copy and paste the '%value%" 3 different times when I can just hold one variable.

jian Over a year ago

yes, you can see demo @H.Kim

H.Kim Over a year ago

that demo showed me exactly what I needed, thank you!

Collectives™ on Stack Overflow

declare variable for a postgresql query using like and union

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related