Error "relation does not exist" in PostgreSQL function()

Question

Function defined:

CREATE OR REPLACE FUNCTION len_chars(t_name VARCHAR, f_name VARCHAR) RETURNS BIGINT AS $$ 
BEGIN 
  SELECT sum(char_length(f_name)) FROM t_name; 
END; 
$$ LANGUAGE plpgsql;

Calling it from psql

SELECT len_chars('public.tag', 'name');

for table "tag" and column "name" returns Error:

psql: ERROR:  relation "t_name" does not exist
LINE 1: SELECT sum(char_length(f_name)) FROM t_name
                                             ^
QUERY:  SELECT sum(char_length(f_name)) FROM t_name
CONTEXT:  PL/pgSQL function len_chars(character varying,character varying) line 1 at SQL statement

Is it possible to choice table name in postgresql functions?

Possible duplicate of [Postgres Dynamic Query Function](https://stackoverflow.com/questions/10639963/postgres-dynamic-query-function) — Sami Kuhmonen, Jun 25 '19 at 11:58

score 2 · Accepted Answer · answered Jun 25 '19 at 11:59

2

You need dynamic SQL for that. And to safely construct a dynamic SQL string, the best way is to use the format() function using %I placeholders to properly deal with quoting identifiers if needed.

CREATE OR REPLACE FUNCTION len_chars(t_name VARCHAR, f_name VARCHAR) 
  RETURNS BIGINT AS $$ 
declare
  l_result bigint;  
BEGIN 
  execute format('SELECT sum(char_length(%I)) FROM %I', f_name, t_name)
    into l_result;
  return l_result; 
END; 
$$ LANGUAGE plpgsql;

answered Jun 25 '19 at 11:59

**I would suggest to use %S instead of %I.** In my case `%I` was wrapping argument values in double quotes. i.e. `SELECT sum(char_length("name")) FROM "public.tag"` and was still getting same error for `t_name`. While `%S` formats the argument value as a string. i.e. `SELECT sum(char_length(name)) FROM public.tag`. – srp Jun 20 '20 at 17:37
@srp: well, you shouldn't pass schema and table name as a single parameter. It would be better to pass them as two parameters then use `from %I.%I` to ensure both names are quoted properly – Jun 20 '20 at 17:39
You are right, It is better to pass them separately. But I am wondering how `%I` is working in your case for passing `public.tag` value for `t_name` as it is giving error in my case. – srp Jun 20 '20 at 17:53
It assumes that `public.tag` is a **single** (table) name that includes a `.` and because a `.` is illegal in an identifier (it's only allowed as a separator), the whole expression needs to be quoted: `"public.tag"` - but obviously you don't have a table with that name. It would be the same if you passed `public tag` or `public&tag` – Jun 20 '20 at 18:16

Error "relation does not exist" in PostgreSQL function()

1 Answers1