postgresql中的EXECUTE语法错误 [英] EXECUTE syntax error in postgresql
问题描述
我想使用execute执行下面的查询,它在select中工作正常,但是当我在触发器中调用它时,出现此错误错误:查询没有结果数据的目的地。我已经尝试过演出,但没有奏效。表中选择插入行的功能。
select insert_new_grade('title0',return3_6(0),return3_6(1),return3_6(2),s.code)
FROMstudentas s
where find_st(s.grade)> = 5;
所以我想到了EXECUTE,但是我得到语法错误。这里是我的尝试:
execute'insert_new_grade('title0',return3_6(0),return3_6(1),return3_6(2 ),s.code)
FROMstudentas s
where find_st(s.grade)> = 5';
有人可以告诉我我做错了什么吗?或者是否有任何其他想法使查询在触发器功能中工作?
以下是触发器:
CREATE OR REPLACE FUNCTION insert_d()
RETURNS TRIGGER AS $$
BEGIN
select insert_new_grade('title0',return3_6(0),return3_6(1),return3_6(2),s.code)
FROMstudentas s
where find_st(s.grade)> = 5;
返回新的;
END;
$$语言plpgsql;
以下是插入函数:
<$创建或替换功能insert_new_diploma(title0字符(100),prof0字符(11),prof1字符(11))
RETURNS VOID AS $$
BEGIN
INSERT INTOd_table(thes0,title,grade,prof,secProf)
VALUES(null,title0,null,prof0,prof1);
END
$$
LANGUAGE'plpgsql';
有时,计算一个表达式或SELECT查询是有用的,但
会丢弃结果,例如在调用具有
副作用但没有有用结果值的函数时。要在PL / pgSQL中执行此操作,请使用
的PERFORM语句
强调我的。
函数内部变化选择
至执行
i want to execute the following query using execute, it works perfect with select but when i call it in trigger i get this error "ERROR: query has no destination for result data ". I 've tried perform but it doesn't work. The function in select insert rows in a table.
select insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), s.code)
FROM "student" as s
where find_st(s.grade)>=5;
So i've thought of EXECUTE but i get syntax errors . Here is my try :
execute 'insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), s.code)
FROM "student" as s
where find_st(s.grade)>=5';
Can someone tell me what im doing wrong ? Or is there any other idea to make the query work in the trigger function? Thanks in advance..
Here is the trigger :
CREATE OR REPLACE FUNCTION insert_d()
RETURNS TRIGGER AS $$
BEGIN
select insert_new_grade('title0', return3_6(0), return3_6(1), return3_6(2), s.code)
FROM "student" as s
where find_st(s.grade)>=5;
return new;
END;
$$ LANGUAGE plpgsql;
and here is the insert function :
CREATE OR REPLACE FUNCTION insert_new_diploma(title0 character(100), prof0 character(11), prof1 character(11))
RETURNS VOID AS $$
BEGIN
INSERT INTO "d_table"(thes0, title, grade, prof, secProf)
VALUES (null, title0, null, prof0, prof1);
END
$$
LANGUAGE 'plpgsql';
41.5.2. Executing a Command With No Result
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement
emphasis mine.
inside function change select
to perform
这篇关于postgresql中的EXECUTE语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!