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?

If you're trying to do this as part of an "insert if not exists" or "update, insert if not exists" type thing, please stop now and read about upsert on PostgreSQL. If that's not what you're doing, maybe edit and explain what your real goal is because it's hard to imagine a funtion like this having a use where there isn't a better way to do it. — Craig Ringer
– Craig Ringer, Commented Jul 4, 2013 at 11:08

Michał Niklas · Accepted Answer · 2013-07-04 11:28:24Z

5

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.

edited Jul 4, 2013 at 11:28

answered Jul 4, 2013 at 10:45

Michał Niklas

54.5k19 gold badges76 silver badges125 bronze badges

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

3 Comments

Craig Ringer Over a year ago

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 Over a year ago

+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 Over a year ago

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).

Craig Ringer · Accepted Answer · 2013-07-04 11:01:33Z

2

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.

edited Jul 4, 2013 at 11:01

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Jul 4, 2013 at 10:08

ElmoVanKielmo

11.4k2 gold badges35 silver badges51 bronze badges

Collectives™ on Stack Overflow

postgresql function error: column name does not exist

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related