The easiest way is https://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349
Using the regclass
CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
INTO result;
END
$func$;
From https://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II
Quote_ident for schema.name
Using single quotes is one protection against SQL injection. The function quote_ident checks its argument and, when the argument contains problematic chars, it returns the argument surrounded by single quotes. It’s quite easy and useful. Problem is with schema.name pair, because the dot is a watched char:
postgres=# select quote_ident('public.foo'); quote_ident -------------- "public.foo" (1 row) postgres=# select * from "public.foo"; ERROR: relation "public.foo" does not exist postgres=# select * from public."foo"; a --- (0 rows) postgres=# select * from "public"."foo"; a --- (0 rows)
We can get around this limitation with custom functions:
CREATE OR REPLACE FUNCTION quote_array(text[]) RETURNS text AS $$ SELECT array_to_string(array(SELECT quote_ident($1[i]) FROM generate_series(1, array_upper($1,1)) g(i)), '.') $$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION quote_schema_ident(text) RETURNS text AS $$ SELECT quote_array(string_to_array($1,'.')) $$ LANGUAGE SQL IMMUTABLE; postgres=# select quote_schema_ident('public.foo tab'); quote_schema_ident -------------------- public."foo tab" (1 row)
What I was trying at first
split_schema text;
split_table text;
test text;
begin
-- split it into schema and table
select into split_schema split_part(table_name, '.', 1);
select into split_table split_part(table_name, '.', 2);
call set_config('search_path', split_schema, true);