在调用包含点 (.) 运算符的参数的 PostgreSQL 函数 时,我遇到了以下错误。
SQL Error [42P01]: ERROR: relation "es.article_data" does not exist Where: PL/pgSQL function es.getrowcount(text) line 6 at EXECUTE....
查询:选择es.article_data中的所有行,返回行数。
功能:
CREATE FUNCTION es.getrowcount (schemawithtable text)
RETURNS VARCHAR(50) AS $msg$
declare
msg VARCHAR(50);
total integer;
begin
execute format('select count(*) from %I where until_ts is null', schemawithtable) into total;
msg := CONCAT(total, ' records are there in ',schemawithtable);
RETURN msg;
END;
$msg$ LANGUAGE plpgsql;
DBeaver的错误跟踪:
org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [42P01]: ERROR: relation "es.article_data" does not exist
Where: PL/pgSQL function es.getrowcount(text) line 6 at EXECUTE
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:487)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:424)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:164)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:416)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:774)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2914)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:110)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:164)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:108)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$17.run(ResultSetViewer.java:3421)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:103)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "es.article_data" does not exist
Where: PL/pgSQL function es.getrowcount(text) line 6 at EXECUTE
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 12 more
我已经想出了解决方案,通过单独传递模式名称来实现。
有效查询语句:select es.getrowcount(schemaname :='es',tablename :='article_data');
CREATE FUNCTION es.getrowcount (schemaname text, tablename text)
...
begin
execute format('select count(*) from %I.%I where until_ts is null',schemaname, tablename) into total;
...
$msg$ LANGUAGE plpgsql;
版本:
PostgreSQL - 10.12
DBeaver(客户端)- 6.3.5
但我想知道为什么它会因为包含点(.)运算符的参数而出现错误?
%I
(标识符)会将输入值用双引号括起来。例如select count(*) from "es.article_data" where until_ts is null
这是无效的语法。而%S
则将参数值格式化为字符串。例如select count(*) from es.article_data where until_ts is null
。 - srp