Postgres. FORMAT doesn't replace type argument with provided value

Question

Honestly, I have no much experience with postgres and sql at all.

I'm Trying to make function with dynamic table and column names using EXECUTE FORMAT(...) query. But whatever I tried FORMAT doesn't consider values provided for type arguments such as %s and %I. And googling didn't help due to lack of useful information. I saw many examples and my code doesn't differentiate from examples but doesn't work. I made fiddle here -> db-fiddle

Here's my DDL code:

CREATE TABLE IF NOT EXISTS tbl (
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

CREATE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS ' 
DECLARE
  text varchar := LOWER($1);
  str varchar  := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(text) - CHAR_LENGTH(REPLACE(text, str, " "))) / CHAR_LENGTH(str);
END; '
LANGUAGE PLPGSQL;


CREATE FUNCTION records_with_string(regclass, varchar, varchar)
RETURNS integer AS '
DECLARE
  result integer;
  tbl ALIAS FOR $1;
  col ALIAS FOR $2;
  str ALIAS FOR $3;
BEGIN
  EXECUTE format("SELECT COUNT(*) FROM %I WHERE occurences_number(%I, %s) > 0", tbl::varchar, col::varchar, str::varchar)
  INTO result;
  RETURN result;
END; '
LANGUAGE PLPGSQL;

And here's query:

INSERT INTO tbl VALUES ('a', 'aaa bbb ccc ddd'), ('b', 'aaa bbb'), ('c', 'ccc ddd'), ('d', 'aaa ccc ddd'), ('e', 'aaa bbb ccc ddd eee');

SELECT "records_with_string"('tbl'::regclass, 'last_name', 'eee') FROM tbl;

The result of the query is:

Query Error: error: column "SELECT COUNT(*) FROM %I WHERE occurences_number(%I, %s) > 0" does not exist

What do I do wrong?

UPDATE db-fiddle doesn't accept $$

Dont use single quotes to quote your function body. Use *dollar quotes* instead. — wildplasser, Jun 16 '20 at 08:18
As @wildplasser said, using $$ would do the trick. But after quickly reading your code I'm not sure you need functions at all. Could you elaborate on your use case? I believe we can find a much less complicated solution. — Jim Jones, Jun 16 '20 at 08:29
@JimJones yeah, I'd use dollar quotes if I could but db-fiddle accepts only single quote. It was my first issue for which I successfully found solution — Emchenko Mikhail, Jun 16 '20 at 12:53
@JimJones well, I'm trying to write function to calculate TF-IDF. So as I think I need few functions to compose them to get TF-IDF function. The part I provided in this question is for calculating IDF part: I want to count # of documents which includes specified substring (btw, I saw smlar plugin but alas it doesn't fit my needs). I'd be happy to know there is simpler solution — Emchenko Mikhail, Jun 16 '20 at 12:58

pifor · Accepted Answer · 2020-06-16T08:39:47.980

You should not use double quotes for string literals but only single quotes and you should use %L for SQL literals when using format:

select * from tbl;
 first_name |      last_name      
------------+---------------------
 a          | aaa bbb ccc ddd
 b          | aaa bbb
 c          | ccc ddd
 d          | aaa ccc ddd
 e          | aaa bbb ccc ddd eee
(5 rows)

CREATE OR REPLACE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS 
$$
DECLARE
  text varchar := LOWER($1);
  str varchar  := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(text) - CHAR_LENGTH(REPLACE(text, str, ' '))) / CHAR_LENGTH(str);
END;
$$ 
LANGUAGE PLPGSQL;
CREATE FUNCTION

CREATE OR REPLACE FUNCTION records_with_string(tbl varchar, col varchar, str varchar)
RETURNS integer AS
$$ 
DECLARE
  result integer;
BEGIN
  EXECUTE format('SELECT COUNT(*) FROM %I WHERE occurences_number(%L, %L) > 0', tbl, col, str)
  INTO result;
  RETURN result;
END;
$$ 
LANGUAGE PLPGSQL;
CREATE FUNCTION

SELECT "records_with_string"('tbl', 'last_name', 'eee') FROM tbl;
 records_with_string 
---------------------
                   0
                   0
                   0
                   0
                   0
(5 rows)

Thank you for the notes about %L and about single quotes. It helped! — Emchenko Mikhail, Jun 16 '20 at 13:03

wildplasser · Answer 2 · 2020-06-16T08:47:28.647

To avoid conflicts with the normal quotes, you need to quote your function body with dollar quotes(basically two $ characters with something in between them. Something can be empty, but begin quote and end quote must match):

CREATE TABLE IF NOT EXISTS ztbl (
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

INSERT INTO ztbl(first_name, last_name) VALUES ('Barack' , 'Obama') , ('Donald' , 'Trump') ;

CREATE FUNCTION occurences_number(varchar, varchar) RETURNS integer AS
$zz$ -- <<-- here
DECLARE
  txt varchar := LOWER($1);
  str varchar := LOWER($2);
BEGIN
  RETURN (CHAR_LENGTH(txt) - CHAR_LENGTH(REPLACE(txt, str, '' ))) / CHAR_LENGTH(str);
END;
$zz$ -- <<-- here
LANGUAGE PLPGSQL;


CREATE FUNCTION records_with_string(regclass, varchar, varchar)
RETURNS integer AS
$zzz$ -- <<-- here
DECLARE
  result integer;
  tbl ALIAS FOR $1;
  col ALIAS FOR $2;
  str ALIAS FOR $3;
BEGIN
  EXECUTE format('
        SELECT COUNT(*) FROM %I
        WHERE occurences_number(%I, %L ) > 0 -- <<-- here
        ;', tbl::varchar, col::varchar, str::varchar)
  INTO result;
  RETURN result;
END;
$zzz$ -- <<-- here
LANGUAGE PLPGSQL;

SELECT records_with_string('ztbl', 'first_name', 'a');

db-fiddle doesn't accept $$ for whatever reason ¯\_(ツ)_/¯ — Emchenko Mikhail, Jun 16 '20 at 13:05

Postgres. FORMAT doesn't replace type argument with provided value

2 Answers2