CREATE FUNCTION statement in an IF block throws error, while running on its own works

Question

I'm new to PostgreSQL (currently on PostgreSQL 13) and may be confusing things from what other SQL encounters I've had (Microsoft SQL).

The goal is to assert there are no values in a table column which would get truncated, then reduce the column length and do the same for the return type of a related function

An example of the code giving the error, so it can be reproduced:

/* 
 CREATE TABLE test_table (id uuid, col_a varchar(100), col_b int); 
 INSERT INTO test_table VALUES (gen_random_uuid(), 'asdf', 1);
 **/

DO $$
BEGIN
    IF NOT EXISTS (SELECT * FROM test_table WHERE character_length(col_a) > 100) THEN
        ALTER TABLE test_table ALTER COLUMN col_a TYPE varchar(100);
        
        DROP FUNCTION IF EXISTS test_function(varchar(100));
    
        CREATE OR REPLACE FUNCTION test_function(test_param varchar(100))
        RETURNS TABLE (
            id uuid,
            col_a varchar(100),
            col_b int
        )
        LANGUAGE plpgsql
        AS $$
        BEGIN
            RETURN QUERY
            SELECT test_table.id AS id, test_table.col_a AS col_a, test_table.col_b AS col_b
            FROM test_table
            WHERE test_table.col_a = test_param;
        END;
        $$;
    ELSE
        RAISE NOTICE 'Cannot Revert. Rows exist that would be truncated!';
    END IF;
END $$

SELECT id, col_a, col_b FROM test_function ('asdf');

/* DROP TABLE test_table */

The error I get is:

SQL Error [42601]: ERROR: syntax error at or near "BEGIN" Position: 400

I have tried an attempt at dynamic code, I've run the function statement on its own - sanity check that works; in fact without the IF block, running each statement either together or separately works fine too.

What's wrong with my approach and how to fix it?

Answer 1

The immediate cause of the error is improper dollar-quoting. This would work:

DO
$do$
BEGIN
   IF NOT EXISTS (SELECT * FROM test_table WHERE character_length(col_a) > 100) THEN
      ALTER TABLE test_table ALTER COLUMN col_a TYPE varchar(100);

      DROP FUNCTION IF EXISTS test_function(varchar(100));

      CREATE OR REPLACE FUNCTION test_function(test_param varchar(100))
        RETURNS TABLE (
         id uuid,
         col_a varchar(100),
         col_b int
        )
      LANGUAGE plpgsql AS
      $func$
      BEGIN
         RETURN QUERY
         SELECT test_table.id, test_table.col_a, test_table.col_b
         FROM   test_table
         WHERE  test_table.col_a = test_param;
      END
      $func$;
   ELSE
      RAISE NOTICE 'Cannot Revert. Rows exist that would be truncated!';
   END IF;
END
$do$;

See:

• Insert text with single quotes in PostgreSQL
• What are '$$' used for in PL/pgSQL

But I wouldn't do most of what you are doing there to begin with. Use the data type text in table and function and be done with it. See:

• Any downsides of using data type "text" for storing strings?
• Should I add an arbitrary length limit to VARCHAR columns?

If you positively need a restriction to a maximum number of characters, still consider text and add a CHECK constraint.

ALTER TABLE test_table ADD CONSTRAINT test_table_col_a_maxlen_200 CHECK (length(col_a) < 201);

Then, if you want to change that constraint later, all you do is:

ALTER TABLE test_table
  DROP CONSTRAINT test_table_col_a_maxlen_200  -- or whatever it was
, ADD  CONSTRAINT test_table_col_a_maxlen_100 CHECK (length(col_a) < 101);

Postgres will verify the CHECK constraint for you automatically, and fail with an error if any row violates it:

ERROR: check constraint "test_table_col_a_maxlen_100" of relation "test_table" is violated by some row

In fairness, you can also just apply the change to varchar(n) in modern Postgres. It will check and fail if any existing row is too long:

ERROR: value too long for type character varying(100)

So you can simplify things even if you stick with varchar(n).