postgresql 选择整数返回函数结果到变量 postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27663650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select integer returning function result into variable postgres
提问by Frank Conry
In Postgres, I'm writing a test script for a function (stored procedure) I wrote. The function returns and integer, the id of the row inserted. In the test script I want to select that id into a variable and show everything has been inserted correctly.
在 Postgres 中，我正在为我编写的函数（存储过程）编写测试脚本。该函数返回整数，即插入行的 id。在测试脚本中，我想将该 id 选择到一个变量中并显示所有内容都已正确插入。
The function looks like this:
该函数如下所示：
CREATE FUNCTION create_factor(p_name VARCHAR(255))
RETURNS integer AS
$$
DECLARE v_insert_id INTEGER;
BEGIN
....
RETURN v_insert_id AS id;
END;
$$ LANGUAGE plpgsql;
The psql script looks like this:
psql 脚本如下所示：
BEGIN;
\i create_factor.sql
DO $$
declare factorId integer;
select create_factor into factorId from /* have tried selecting * as well */
create_factor(
'my factor'::VARCHAR(255)
);
\x
select * from factors where name='my factor' and id=factorId;
\x
select k.key_name, v.value
from factors f
join factor_type_key_store k on k.factor_type_id = f.factor_type_id
join factor_key_value_store v on v.factor_type_key_store_id=k.id ;
END$$;
ROLLBACK;
The error I get is as follows:
我得到的错误如下：
psql:create_factor_test.sql:31: ERROR: invalid type name "create_factor.id into factorId from
psql:create_factor_test.sql:31: 错误：无效的类型名称“create_factor.id into factorId from
clearly referring to the select intostatement. I've switched up both the function definition and the script quite a bit. Not sure what I'm missing.
明确指的是select into声明。我已经对函数定义和脚本进行了相当多的修改。不知道我错过了什么。
采纳答案by Threadid
You need to use an EXECUTE .... INTO to assign your variable like this:
您需要使用 EXECUTE .... INTO 来分配您的变量，如下所示：
See documentation here
请参阅此处的文档
Function returns 1:
函数返回 1：
CREATE FUNCTION create_factor( p_name VARCHAR(255)
)
RETURNS integer AS $$
DECLARE v_insert_id INTEGER;
BEGIN
v_insert_id:=1;
RETURN v_insert_id AS id;
END;
$$ LANGUAGE plpgsql;
EXECUTE INTO assign the function result to your variable:
EXECUTE INTO 将函数结果分配给您的变量：
DO $$
declare factorId integer;
BEGIN
EXECUTE 'select * from
create_factor(
''my factor''::VARCHAR(255)
);' INTO factorId; /* have tried selecting * as well */
RAISE NOTICE 'factorID = %', factorId;
EXECUTE 'WITH f AS (select * from factors where name=''my factor'' and id=' || factorId || ')
select k.key_name, v.value from f join factor_type_key_store k on k.factor_type_id = f.factor_type_id join factor_key_value_store v on v.factor_type_key_store_id=k.id;';
END$$;
回答by Erwin Brandstetter
Variable names and column aliases are not visibleoutside a function. Namescan be set with OUTparameters. For a simple function returning a scalar value like yours the default column name defaults to the name of the function.
变量名和列别名在函数外不可见。名称可以通过OUT参数设置。对于像您这样返回标量值的简单函数，默认列名称默认为函数名称。
CREATE FUNCTION create_factor(p_name text)
RETURNS integer AS
$func$
DECLARE
v_insert_id INTEGER;
BEGIN
....
RETURN v_insert_id; -- AS id -- pointless noise
END;
$$ LANGUAGE plpgsql;If you want to use a DOstatementfor a test (I don't see the point, but following your lead):
如果你想在测试中使用一个DO语句（我不明白这一点，但跟随你的领导）：
DO
$do$
DECLARE
factor_id integer := create_factor('my factor');
BEGIN
RAISE NOTICE 'New factor row: %'
, (SELECT f FROM factors f WHERE name = 'my factor' AND id = factor_id);
END
$do$;
I assume you are aware that code inside a DOstatement is PL/pgSQL by default, not plain SQL?
我假设您知道DO语句中的代码默认是 PL/pgSQL，而不是普通 SQL？
You can declare a variable in plpgsql code and assign to it on the same line.
您可以在 plpgsql 代码中声明一个变量并在同一行分配给它。
But you cannot RETURNanything from a DOstatement. You'd need another function for that. Raising a NOTICEprobably does the job for you? Not knowing what you want to see, I return the whole row in the example.
但是你不能RETURN从DO声明中得到任何东西。你需要另一个功能。提高一个NOTICE可能适合你的工作？不知道你想看到什么，我返回了示例中的整行。
