postgresqlpostgresql-12create-function

Unterminated dollar-quoted string creating PostgreSQL function


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


Solution

  • 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)