我在表中遇到数据插入问题"pm_feature_id"这是表中的主键，它与其他 2 个表相互链接

Question

我在表中遇到数据插入问题 "pm_feature_id"是表中的主键，它与其他 2 个表相互链接。主键是自动增量的，它声明为变量，我正在使用下面的代码将数据插入表中">

我正在插入数据，但我正在尝试使用 select 语句返回数据"pm_feature_name，pm_component_id">

-- FUNCTION: project.fn_insert_features(character varying, integer, character varying, character varying, integer, integer)
-- DROP FUNCTION project.fn_insert_features(character varying, integer, character varying, character varying, integer, integer);
CREATE OR REPLACE FUNCTION project.fn_insert_features(
featurename character varying,
compntid integer,
descrptn character varying,
helpfilename character varying,
mediatypeid integer,
accntemplyid integer)
RETURNS TABLE(pm_feature_name character varying, pm_component_id integer)
LANGUAGE "sql"
COST 100
VOLATILE 
ROWS 1000 
AS $BODY$
DECLARE
v_id integer;
BEGIN
Insert into project.pm_features
(pm_feature_name,pm_component_id,pm_description)values (featurename,compntid,descrptn)
RETURNING pm_feature_id INTO v_id;

Insert into project.pm_help_files
(component_feature_id,help_file_name, media_type_id)values (v_id,helpfilename,mediatypeid);
Insert into project.pm_point_of_contacts
(component_feature_id,pm_account_employee_id)values(v_id,accntemplyid);
select pm_feature_name,pm_component_id from project.pm_features
END;
$BODY$;
ALTER FUNCTION project.fn_insert_features(character varying, integer, character varying, character varying, integer, integer)
OWNER TO pattesaadmin;

错误

错误：在"整数"处或附近出现语法错误第 21 行：v_id整数; ^ SQL 状态： 42601 字符： 637

Answer 1

试试这个：

CREATE OR REPLACE FUNCTION project.fn_insert_features(
featurename character varying,
compntid integer,
descrptn character varying,
helpfilename character varying,
mediatypeid integer,
accntemplyid integer)
RETURNS TABLE(pm_feature_name character varying, pm_component_id integer)
LANGUAGE plpgsql
COST 100
VOLATILE 
ROWS 1000 
AS $BODY$
DECLARE
v_id integer;
BEGIN
Insert into project.pm_features
(pm_feature_name,pm_component_id,pm_description)values (featurename,compntid,descrptn)
RETURNING pm_feature_id INTO v_id;

Insert into project.pm_help_files
(component_feature_id,help_file_name, media_type_id)values (v_id,helpfilename,mediatypeid);
Insert into project.pm_point_of_contacts
(component_feature_id,pm_account_employee_id)values(v_id,accntemplyid);
return query select pm_features.pm_feature_name, pm_features.pm_component_id from project.pm_features;
END;
$BODY$;

ALTER FUNCTION project.fn_insert_features(character varying, integer, character varying, character varying, integer, integer)
OWNER TO pattesaadmin;

请注意，我将"sql"更改为plpgsql，并在select语句前面添加了"返回查询"，并在语句末尾添加了分号。 希望这就足够了，我没有表格描述来完全测试。

最好的问候，
比亚尼