我有这个postgresql函数
CREATE OR REPLACE FUNCTION sp_select_test(_id bigint)
RETURNS void AS
$BODY$
BEGIN
copy (select sub.id
from (
SELECT id FROM sim s where id = _id) sub) TO '/tmp/results.tsv';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sp_select_test(bigint)
OWNER TO postgres;
当我运行时
select * from sp_select_test(264);
我得到错误-列"_id"不存在
如果我将函数中的变量_id替换为一个值,比如264,那么函数就工作了。也就是说,如果我使用id=264 而不是id=_id
功能失败的原因是什么?
CREATE OR REPLACE FUNCTION sp_select_test(_id bigint)
RETURNS void AS
$BODY$
BEGIN
execute $$copy (select sub.id
from (
SELECT id FROM sim s where id = $$||_id||$$) sub) TO '/tmp/results.tsv'$$;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sp_select_test(bigint)
OWNER TO postgres;