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.

klin · Accepted Answer · 2015-10-13T16:33:11.593

25

Instead of

execute 'select 1 into i'    -- error

you should use

execute 'select 1' into i

edited Oct 13 '15 at 16:33

answered Oct 13 '15 at 16:11

klin

99,138
12
177
203

Erwin Brandstetter · Answer 2 · 2019-03-30T01:48:17.863

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.

EXECUTE of SELECT ... INTO is not implemented

2 Answers2

Major points

Linked