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