EXECUTE of SELECT ... INTO is not implemented

Question

I am trying to run this function in PostrgeSQL:

CREATE OR REPLACE FUNCTION create_partition_and_insert()
RETURNS trigger AS
$BODY$
DECLARE
partition VARCHAR(25);
_date text;
BEGIN
EXECUTE 'SELECT REPLACE(' || quote_literal(NEW.date) || ',''-'',''_'') into _date';
partition := TG_RELNAME || '_' || _date || ‘p’;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_RELNAME || ');';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

But on running this I get this as error:

ERROR:  EXECUTE of SELECT ... INTO is not implemented
HINT:  You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS instead.
CONTEXT:  PL/pgSQL function create_partition_and_insert() line 6 at EXECUTE statement

I have no clue why this is happening and none of the solutions provided online are helping me here.

Answer 1

Instead of

execute 'select 1 into i'    -- error

you should use

execute 'select 1' into i

Answer 2

The error message is the least of the problems in this function. Consider a complete rewrite.

Assuming the column date is actually data type date:

CREATE OR REPLACE FUNCTION create_partition_and_insert()
  RETURNS trigger AS
$func$
DECLARE
   _partition text := quote_ident(TG_RELNAME
                               || to_char(NEW.date,'_YYYY_MM_DD"p"'));
BEGIN
   IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = _partition) THEN
      EXECUTE format('CREATE TABLE %s (CHECK (date = %L)) INHERITS (%I);'
                   , _partition, NEW.date, TG_RELNAME);

      RAISE NOTICE 'A new partition has been created: %', _partition;
   END IF;

   EXECUTE format('INSERT INTO %s SELECT ($1).*', _partition)
   USING NEW;

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

Major points

• You don't need dynamic SQL in your first statement at all (the source or your syntax error). Actually, you don't need the whole statement nor the variable. I radically simplified the name concatenation. Details for to_char() in the manual.

• Ignoring the typographical quotes in ‘p’ - those are probably just c/p artefacts.

• Assignments are comparatively expensive in plpgsql. Adapt your programming style and reduce variables and assignments.

• Don't convert the whole row to its text representation, concatenate and then cast it back. That's needlessly expensive, convoluted and error-prone. Pass the value directly in a USING clause to EXECUTE like demonstrated.

• Don't raise the notice before it's done. RAISE is not rolled back in the case of an exception leading to potentially misleading log entries.

• If you have more than one schema in your database (which is common), your code is still unsafe. You need to schema-qualify table names or SET the search_path for the function.

Related answers with more details:

• How to check if a table exists in a given schema

• Creating a trigger for child table insertion returns confusing error

• INSERT with dynamic table name in trigger function

• How does the search_path influence identifier resolution and the "current schema"

Answer 3

PostgreSQL official documentation (as on 12 July 2022) clearly calls out that as below:

SELECT INTO is not currently supported within EXECUTE; instead, execute a plain SELECT command and specify INTO as part of the EXECUTE itself.

Something like below worked for me.

v_query_string = 'SELECT count(*) FROM foo';
EXECUTE v_query_string into v_count;