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

Sami Kuhmonen
– Sami Kuhmonen

2019-06-25 11:58:58 +00:00
Commented Jun 25, 2019 at 11:58 — Sami Kuhmonen
– Sami Kuhmonen, Commented Jun 25, 2019 at 11:58
Thank you @Sami, usefull link.

magic_turn
– magic_turn

2019-06-25 15:32:20 +00:00
Commented Jun 25, 2019 at 15:32 — magic_turn
– magic_turn, Commented Jun 25, 2019 at 15:32

user330315 · Accepted Answer · 2019-06-25 11:59:23Z

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, 2019 at 11:59

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

srp Over a year ago

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.

user330315 Over a year ago

@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

srp Over a year ago

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.

user330315 Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related