EXECUTE of SELECT ... INTO 未实现 [英] EXECUTE of SELECT ... INTO is not implemented

问题描述

我正在尝试在 PostrgeSQL 中运行此函数:

创建或替换函数 create_partition_and_insert()返回触发 AS$BODY$宣布分区 VARCHAR(25);_date 文本；开始EXECUTE 'SELECT REPLACE(' || quote_literal(NEW.date) || ',''-'',''_'') into _date';分区:= TG_RELNAME ||'_' ||_日期 ||'p';IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THENRAISE NOTICE '分区已创建 %',partition;执行'创建表' ||分区 ||' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_RELNAME || ');';万一;执行'插入' ||分区 ||' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';返回空；结尾;$BODY$语言 plpgsql;

但是在运行这个时我得到这个错误:

<块引用>

ERROR: EXECUTE of SELECT ... INTO 未实现提示:您可能希望使用 EXECUTE ... INTO 或 EXECUTE CREATE TABLE ... AS 代替.上下文:PL/pgSQL 函数 create_partition_and_insert() 第 6 行 EXECUTE 语句

我不知道为什么会发生这种情况，在线提供的解决方案都没有帮助我.

解决方案

代替

execute 'select 1 into i' -- 错误

你应该使用

执行 'select 1' into i

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.

解决方案

Instead of

execute 'select 1 into i'    -- error

you should use

execute 'select 1' into i

这篇关于EXECUTE of SELECT ... INTO 未实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！