PostgreSQL function : relation does not exist error

Question

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 ?

Akhilesh Mishra · Accepted Answer · 2020-06-20T12:43:06.157

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

So `%I` (identifier) is wrapping input value in double quotes. i.e. `select count(*) from "es.article_data" where until_ts is null` which is invalid syntax. While `%S` formats the argument value as a string. i.e. `select count(*) from es.article_data where until_ts is null`. — srp, Jun 20 '20 at 14:57

PostgreSQL function : relation does not exist error

1 Answers1