Luca Ferrari: Table name as function arguments: a few checks

How to check if a given table name exists and where to find it. Table name as function arguments: a few checks Often I write some piece of code, usually a function or a procedure, that must operate dynamically on a table. To achieve this, I often pass the table name as an argument to the function. The function should always check that the table exists and, moreover, the function should always use the fully qualified name of the table to avoid schema conflicts and search_path pollution problems. Last, sometime I use a relative name when I do pass the table as an argument, sometime I want to pass a fully qualified name to the function. I’ve a template for doing this minimal checks, clearly it is just an idea on how to improve your own functions when dealing with table names. Imagine a simple function that accepts a table name, as follows: CREATE OR REPLACE FUNCTION f_do_stuff_on_table( t_name text ) RETURNS bool AS $CODE$ DECLARE s_name text; info text[]; pg_version int; qualified_name text; BEGIN -- parse the schema name info := parse_ident( t_name ); IF array_length( info, 1 ) = 2 THEN s_name := info[ 1 ]; t_name := info[ 2 ]; ELSE -- try to understand if PostgreSQL 15 or higher SELECT setting::int INTO pg_version FROM pg_settings WHERE name = 'server_version_num'; IF pg_version >= 150000 THEN SELECT current_role INTO s_name; ELSE s_name := 'public'; END IF; END IF; -- check if the table exists PERFORM c.oid FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname = s_name AND c.relname = t_name; IF NOT FOUND THEN RAISE 'Table %.% does not exist, cannot proceed!', s_name, [...]