PostgreSQL function : relation does not exist error

I am getting below error while calling Postgresql function with argument containing dot (.) operator.

SQL Error [42P01]: ERROR: relation "es.article_data" does not exist Where: PL/pgSQL function es.getrowcount(text) line 6 at EXECUTE.... 

Query: select es.getrowcount(schemawithtable :='es.article_data');

Function:

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; 

Error traces from 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 

I have figure out the workaround by passing schema name separately.

Working Query: 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; 

Versions: PostgreSQL - 10.12 DBeaver (client) - 6.3.5

But I want to know why it is giving an error for argument containing dot (.) operator ?

1 Answer

When you are creating your query with format and %I. It is creating your query like below

With Single Parameter:

select count(*) from "es.article_data" where until_ts is null 

With Double Parameter:

select count(*) from "es"."article_data" where until_ts is null 

In first case it will show error because you can not use table name with schema like this. But in second case it worked perfectly as this is the right way to use naming convention.

If you want to use the first method only. you should use %s instead of %Ilike below

CREATE FUNCTION es.getrowcount (schemawithtable text) RETURNS VARCHAR(50) AS $msg$ declare msg VARCHAR(50); total integer; begin execute format('select count(*) from %s where until_ts is null', schemawithtable) into total; msg := CONCAT(total, ' records are there in ',tablename); RETURN msg; END; $msg$ LANGUAGE plpgsql; 

NOTE : This will work properly if your table names and schema names are in lower case only