Unterminated dollar-quoted string creating PostgreSQL function

Question

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

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. — J Spratt, Jul 07 '20 at 12:38

Adrian Klaver · Accepted Answer · 2020-07-07T14:40:06.083

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)

Is it possible to return multiple rows from an EXECUTE inside the function test? I mean, changing `returns void` by `returns table (...)` — coterobarros, Jul 07 '20 at 14:54
The docs are your friend. Start here [plpgsql](https://www.postgresql.org/docs/current/plpgsql.html). There you have [looping](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING) and there you have "The ```FOR-IN-EXECUTE``` statement is another way to iterate over rows: ..." — Adrian Klaver, Jul 07 '20 at 15:02

Unterminated dollar-quoted string creating PostgreSQL function

1 Answers1