我需要创建plpgsql方法,这些方法使用当前行值,而不在update命令中传递参数。
我试过了

create temp table test ( test text, result text ) on commit drop;
insert into test values ('1','');

CREATE OR REPLACE FUNCTION public.gettest() RETURNS text AS $$
DECLARE
  comp text := NULL;

BEGIN
EXECUTE 'SELECT ''Current row is '' ||test.test' INTO comp;
RETURN comp;
END; $$ LANGUAGE plpgsql STRICT STABLE;

update test set result = 'Result: ' || gettest();

但有例外
ERROR:  missing FROM-clause entry for table "test"
LINE 1: SELECT 'Current row is ' ||test.test
                                   ^
QUERY:  SELECT 'Current row is ' ||test.test
CONTEXT:  PL/pgSQL function gettest() line 6 at EXECUTE statement
********** Error **********

ERROR: missing FROM-clause entry for table "test"
SQL state: 42P01
Context: PL/pgSQL function gettest() line 6 at EXECUTE statement

如何修复?
如何在不向plpgsql方法参数传递vaue的情况下修复?

最佳答案

没有所谓的“隐式当前行”。您必须将函数作为参数传递给它所需的任何内容。但是,如果您希望:

create temp table test (val text, result text ) on commit drop;
insert into test values ('1','');
insert into test values ('2','');

CREATE OR REPLACE FUNCTION gettest(p_test test) RETURNS text AS $$
DECLARE
  comp text := NULL;
BEGIN
   comp := 'Current row is '||p_test.val;
RETURN comp;
END; $$ LANGUAGE plpgsql STRICT STABLE;

update test set result = 'Result: ' || gettest(test);

我不得不将列test重命名为其他名称,否则调用gettest(test)将引用列而不是整个表(=行),因此它不起作用。

关于sql - 如何在plpgsql函数中获取当前行值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/32038243/

10-13 04:45