How to run plpgsql without creating a function?

Question

I'd like to programmatically run a SQL in Postgres without creating a function.

Reason: to make sure my plpgsql works beforehand AND to "explain analyze" the query before committing it to a function.

I'm new to Postgres and I thought this would be simple. I couldn't find any example out there. Maybe it isn't possible? How can the code below work?

DO
$body$
DECLARE
  v_name_short VARCHAR;
BEGIN

v_name_short := 'test Account 1';

     RETURN QUERY
        SELECT 
            a.name_short, 
            a.name_long
        FROM enterprise.account a 
        WHERE 
            CASE WHEN v_name_short IS NOT NULL THEN
               LOWER(a.name_short) = LOWER(v_name_short)
            ELSE
               1 = 1   
            END;
END;
$body$
LANGUAGE 'plpgsql';

Again, the goal here is TESTING my SQL, like in this case I want to make sure my CASE statement is still using an index I created (LOWER(name_short)). Anyhow, I get this error message:

ERROR: cannot use RETURN QUERY in a non-SETOF function

Is what I'm asking possible in Postgres? If not, is there a way to query analyze plans inside a function?

Answer 1

An anonymous code block returns void. However, you can use a trick with a temporary table, e.g.

CREATE TEMP TABLE IF NOT EXISTS trace (name_short text, name_long text);

DO
$body$
DECLARE
    v_name_short VARCHAR;
BEGIN

    v_name_short := 'test Account 1';

    INSERT INTO trace 
        SELECT 
            a.name_short, 
            a.name_long
        FROM enterprise.account a 
        WHERE 
            CASE WHEN v_name_short IS NOT NULL THEN
               LOWER(a.name_short) = LOWER(v_name_short)
            ELSE
               1 = 1   
            END;
END;
$body$
LANGUAGE 'plpgsql'; 

SELECT * FROM trace;
-- DROP TABLE trace;

---

With EXPLAIN ANALYSE you can analyse only a single plain sql query, not a function, a do block nor a script. So you can try:

EXPLAIN ANALYSE
    SELECT 
        a.name_short, 
        a.name_long
    FROM enterprise.account a 
    WHERE 
        CASE WHEN 'test Account 1' IS NOT NULL THEN
           LOWER(a.name_short) = LOWER('test Account 1')
        ELSE
           1 = 1   
        END;

Note that in this case you cannot use the variable beacuse it won't be recognized by the planner, use the literal instead.

Answer 2

A do anonymous code block always returns void:

The code block is treated as though it were the body of a function with no parameters, returning void

To execute queries inside a do block use perform

do $$
    begin
    perform * from t;
    end
$$;

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT