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?

Why do you use a `do` block to run an `explain`? why can't you simply run `explain select ...`? — , Jul 13 '16 at 17:59

klin · Accepted Answer · 2016-07-14T13:56:56.507

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.

Klin, thanks for your answer. I'm just surprised I can't run this cleanly in Postgres. Although your solution executes, I can't run an "explain analyze" from pgAdmin, as it bombs right in the CREATE statement (which can't be analyzed). I guess I need to figure out a way to analyze a query plan inside a function... — arnold, Jul 13 '16 at 16:43
You can analyse only a single plain sql query, not a function, a do block nor a script. In the case try `explain analyse select ...` but you cannot use the variable. Put the literal `'test Account 1'` instead. — klin, Jul 13 '16 at 18:40
Klin, a favor: would you mind updating your answer with the comment you just posted? Then the answer will be completed and I can mark it as accepted. Thanks so much again for your help! — arnold, Jul 14 '16 at 13:38

Clodoaldo Neto · Answer 2 · 2016-07-13T15:35:08.553

8

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

edited Jul 13 '16 at 15:35

answered Jul 13 '16 at 15:26

Clodoaldo Neto

118,695
26
233
260

Thanks, but is there a way to execute the code above with an alternate syntax? – arnold Jul 13 '16 at 15:30
We're one step closer, but now I get this: "Query returned successfully with no result in 28 msec". Now, there is a result... but like you said "DO" always returns void. Is there a way to run the query without DO then? Thanks again for your help! – arnold Jul 13 '16 at 15:48

Erwin Brandstetter · Answer 3 · 2023-07-17T22:08:06.020

There is a third option between a regular PL/pgSQL function and a DO statement: a temporary function. It's not documented explicitly, but it's just using standard Postgres features.

And you actually can get detailed query plans for SQL statements nested in PL/pgSQL code blocks with the additional module auto-explain. See:

Get query plan for SQL statement nested in a PL/pgSQL function

But you'll have to test your function with values for each branch in your CASE expression to make sure to cover everything. Postgres only does superficial syntax checks on CREATE FUNCTION.

Prepared statements may be another option to work with. PL/pgSQL handles SQL statements much like prepared statements internally:

Difference between language sql and language plpgsql in PostgreSQL functions

How to run plpgsql without creating a function?

3 Answers3