Postgresql create function syntax error near BEGIN

user_5078531 0
Viewed 614

This is the function I'm trying to execute via migration script.

CREATE OR REPLACE FUNCTION test(key1 text)
RETURNS void AS $$ 
BEGIN
  INSERT INTO table1(c1) VALUES($1);
END;
$$ LANGUAGE 'plpgsql';

The above script executes successfully, but when I tried to with a .NET Core idempotent migration script it gives an error ERROR: syntax error at or near "BEGIN"

This is the migration script.

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM __EFMigrationsHistory WHERE "MigrationId" = 'migrationid') THEN
    CREATE OR REPLACE FUNCTION test(key1 text)
    RETURNS void AS $$ 
    BEGIN
      INSERT INTO table1(c1) VALUES($1);
    END;
    $$ LANGUAGE 'plpgsql';
    END IF;
END $$;

I also tried adding EXECUTE still same error.

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM __EFMigrationsHistory WHERE "MigrationId" = 'migrationid') THEN
    EXECUTE('CREATE OR REPLACE FUNCTION test(key1 text)
    RETURNS void AS $$ 
    BEGIN
      INSERT INTO table1(c1) VALUES($1);
    END;
    $$ LANGUAGE ''plpgsql'';');
    END IF;
END $$;

How can I create a SQL statement to create/replace the function by checking the migrationhistory table?

entity-framework-core plpgsql postgresql sql
1 Answers

The parser is getting confused by the repetitive usage of dollar quotes. Just give one of them an alias and it should work, e.g.

DO $body$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM __EFMigrationsHistory WHERE "MigrationId" = 'migrationid') THEN
    EXECUTE('CREATE OR REPLACE FUNCTION test(key1 text)
    RETURNS void AS $$ 
    BEGIN
      INSERT INTO table1(c1) VALUES($1);
    END;
    $$ LANGUAGE ''plpgsql'';');
    END IF;
END $body$;
user_22753880
Related