PL/PGSQL Operator does not exist: information_schema.sql_identifier

Question

I'm writing a function to write a dynamic query.

This is the original query without function

SELECT b.column_name, a.default_flag, CAST(AVG(a.payment_ratio) AS NUMERIC), CAST(MAX(a.payment_ratio) AS NUMERIC)  FROM user_joined a, information_schema.columns b where b.column_name = 'payment_ratio' group by a.default_flag, b.column_name

Then, I put it into a function like this

CREATE OR REPLACE FUNCTION test4(col text)
RETURNS TABLE(
    col_name TEXT,
    default_flag bigint,
    average NUMERIC,
    maximum NUMERIC) AS $$
BEGIN 
RETURN QUERY EXECUTE FORMAT
('SELECT CAST(b.column_name AS TEXT), a.default_flag, CAST(AVG(a.'||col||') AS NUMERIC), CAST(MAX(a.'||col||') AS NUMERIC)  FROM user_joined a, information_schema.columns b where b.column_name = %I group by a.default_flag, b.column_name', col);
END; $$
LANGUAGE PLPGSQL;

When I try to run

SELECT * FROM test4('payment_ratio')

I get this error

ERROR:  operator does not exist: information_schema.sql_identifier = double precision
LINE 1: ... information_schema.columns b where b.column_name = payment_...

Is there anything wrong with my function?

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-02-08T09:07:32

The columns in information_schema have the (somewhat strange) data type sql_identifier and that can't be compared directly to a text value. You need to cast it in the SQL query.

You are also using the %I incorrectly. In the join condition the column name is a string constant so you need to use %L there. In the SELECT list, it's an identifier, so you need to use %I there.

CREATE OR REPLACE FUNCTION test4(col text)
RETURNS TABLE(
    col_name TEXT,
    default_flag bigint,
    average NUMERIC,
    maximum NUMERIC) AS $$
BEGIN 
RETURN QUERY EXECUTE 
   FORMAT ('SELECT CAST(b.column_name AS TEXT), 
                   a.default_flag, CAST(AVG(a.%I) AS NUMERIC), 
                   CAST(MAX(a.'||col||') AS NUMERIC)  
            FROM user_joined a
              JOIN information_schema.columns b ON b.column_name::text = %L 
            group by a.default_flag, b.column_name', col, col);
END; $$
LANGUAGE PLPGSQL;

PL/PGSQL Operator does not exist: information_schema.sql_identifier

1 Answers