postgresql function error: column name does not exist

Question

i've implemented a function that check if a value appears in a specific row of a specific table:

CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$

DECLARE res BOOLEAN;

BEGIN 
    SELECT table_column INTO res
    FROM table_name 
    WHERE table_column = id;

    RETURN res;
END;

$$ LANGUAGE plpgsql

i've create and fill a simple test table for try this function:

CREATE TABLE tab(f INTEGER);

and i call function like

SELECT check_if_exist(10, tab, f);

but i occurs in this error:

ERROR:  column "prova" does not exist
LINE 1: SELECT check_if_exist(10, tab, f);
                              ^


********** Error **********

ERROR: column "tab" does not exist
SQL state: 42703
Character: 27

why?

Solution 1

Your code has no chance to work - when dealing with different tables in PLPGSQL you need to utilize dynamic queries, so EXECUTE is required - http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
But first of all - there is nothing bad in using PostgreSQL EXISTS - http://www.postgresql.org/docs/current/static/functions-subquery.html#AEN15284 instead of inventing your own - performance of your solution will be significantly worse than using included batteries...
Hopefully this is helpful. Good luck.

Solution 2

In addition to Elmo response you must be careful with types. You have got:

ERROR: column "tab" does not exist

because SQL parser do not know how to deal with tab which is without quote. Your query must be like:

SELECT check_if_exist(10, 'tab', 'f');

As Elmo answered you use dynamic query, so even if you quote tab you will got error:

ERROR:  relation "table_name" does not exist

so you can use EXECUTE, example:

CREATE OR REPLACE FUNCTION check_if_exist(id INTEGER, table_name varchar, table_column varchar) RETURNS BOOLEAN AS $$
    DECLARE
        sql varchar;
        cnt int;
    BEGIN 
        sql := 'SELECT count(*) FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(table_column) || '=$1';
        RAISE NOTICE 'sql %', sql;
        EXECUTE sql USING id INTO cnt;
        RETURN cnt > 0;
    END;
$$ LANGUAGE plpgsql

You can also use VARCHAR instead of character(N) in function arguments and use CREATE OR REPLACE FUNCTION ... instead of just CREATE FUNCTION ... which is very handy at debugging.

Comments

[Craig Ringer]

Please never show EXECUTE with direct string concatentation. It makes the function a vector for SQL injection. The above should be written EXECUTE format('SELECT count(*) FROM %I WHERE %I = $1', table_name, table_column) USING id INTO cnt; or, for older PostgreSQL versions without format, should wrap the table and column names in quote_ident calls.

[ElmoVanKielmo]

+1 for the answer, however @CraigRinger is right with his concerns, so +1 for him too. It's nice to show OP how to make dynamic queries and prevent SQL injection, but I still say that writing this exact function is reinventing the wheel.

[Michał Niklas]

Thanks, I have just edited answer. I do not see this function very useful when we can use select exists (select * from my_table where my_column=my_value).