sql - SQLExceptionHelper Invalid Input syntax for integer (PL/pgSQL)
问题描述
I want to get an integer from a database query:
SELECT CAST(concat('id_',substr_of_jointable) AS INTEGER) into integervalue
FROM books_authors where id_books = booksvalue
ORDER BY id_books DESC
LIMIT 1;
subsr_of_jointable
is a TEXT with value authors
. However I always get an error:
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: invalid input syntax for integer: "id_authors"
Wobei: SQL statement "SELECT CAST(concat('id_',substr_of_jointable)AS INTEGER) FROM books_authors where id_books = books_value ORDER BY id_books DESC LIMIT 1"
PL/pgSQL function books_ins_trig_proc() line 125 at SQL statement
Does anyone have an idea why? The column id_books
id_authors
is an integer value in the database.
解决方案
Assuming you're trying to build a dynamic query inside a PL/pgSQL function, you might want to take a look at this approach.
Data sample
CREATE TABLE t (id_authors INT);
INSERT INTO t VALUES (1);
Function
CREATE OR REPLACE FUNCTION myfunction(TEXT) RETURNS INT
LANGUAGE 'plpgsql' AS $BODY$
DECLARE i INT;
BEGIN
EXECUTE 'SELECT id_'|| $1 ||' FROM t LIMIT 1;' INTO i;
RETURN i;
END;
$BODY$;
This example is only to shows how you can concatenate your strings to create a column name inside your dynamic query.
Calling the function
SELECT * FROM myfunction('authors');
myfunction
------------
1
(1 Zeile)
Further reading:
推荐阅读
- r - R中参数的“类型”(列表)无效
- python - 用户模型的外键未按预期工作
- android - Retrofit2.Retrofit 的改造类文件未找到
- c# - WIX 安装程序无法正确运行 C# 进程
- python - Python eval() 乘法不正确
- f# - 同时使用多个 F# 类型提供程序的性能影响
- python-2.7 - 序列化和反序列化之间的底层类变化
- javascript - 将所有 HTML 表值传递给 PHP 以使用 Javascript 将它们存储在数据库中
- windows - Robotframework-WhiteLibrary:Windows 应用自动化:单击按钮关键字导致失败
- python - 重新分配的数组(Python)