1

Using PostgreSQL 12.3, I am having some trouble trying to validate this simple chunk of plpgsql code

create or replace function test() 
returns void 
as $$
begin
  prepare plan as select 1;
  execute plan;
end;
$$ language plpgsql;

Error is

Unterminated dollar-quoted string at or near "$$ begin prepare plan as select 1;"

I have tried with and without ; after end. I have also tried with sql instead of plpgsql. Any idea of whats is wrong?

This is a db-fiddle to quickly test the code:

https://www.db-fiddle.com/f/KgRZcxXqJs2Lwe284Mj5y/3

4
  • 5
    Let me guess, HeidiSQL? Commented Jul 7, 2020 at 12:31
  • 5
    I would guess that whichever client you're using to run the SQL does not support dollar quoting. You'll need to replace each pair of $ with a single '. Keep in mind if there are any single quotes within the body of the function, you'll need to escape them. As a bonus, consider checking out DBeaver for your SQL client. Commented Jul 7, 2020 at 12:38
  • Ok, I see. This is just a client's inability Commented Jul 7, 2020 at 13:36
  • J Spratt, thank you for introducing DBeaver, just amazing! Commented Jul 7, 2020 at 13:41

1 Answer 1

1

The issue is not with the $$ quoting:

create or replace function test() 
returns void 
as $$
begin
  prepare plan as select 1;
  execute plan;
end;
$$ language plpgsql;
CREATE FUNCTION

 select test();
ERROR:  column "plan" does not exist
LINE 1: SELECT plan
               ^
QUERY:  SELECT plan
CONTEXT:  PL/pgSQL function test() line 4 at EXECUTE

When you run this in the dbfiddle the full error output is:

 Schema Error: error: unterminated dollar-quoted string at or near "$$ begin prepare plan as select 1;"
Schema Error: error: prepared statement "plan" does not exist
Schema Error: error: unterminated dollar-quoted string at or near "$$ language plpgsql;"
Query Error: error: function test() does not exist 

The issue is that EXECUTE inside plpgsql is its own command:

https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

I would use the plpgsql form. This works:

create or replace function test() 
returns void 
as $$
begin
  prepare plan as select 1;
  EXECUTE 'execute plan';
  RAISE NOTICE 'Made it';
  DEALLOCATE plan;
end;
$$ language plpgsql;

select test();
NOTICE:  Made it
 test 
------
 
(1 row)


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

3 Comments

Thank you Adrian!
Is it possible to return multiple rows from an EXECUTE inside the function test? I mean, changing returns void by returns table (...)
The docs are your friend. Start here plpgsql. There you have looping and there you have "The FOR-IN-EXECUTE statement is another way to iterate over rows: ..."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.