This can be further simplified and improved:
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$;
Call with schema-qualified name (see below):
SELECT some_f('myschema.mytable'); -- would fail with quote_ident()
SELECT some_f('"my very uncommon table name"');
OUT parameter to simplify the function. You can directly select the result of the dynamic SQL into it and be done. No need for additional variables and code.
EXISTS does exactly what you want. You get
true if the row exists or
false otherwise. There are various ways to do this,
EXISTS is typically most efficient.
You seem to want an integer back, so I cast the
boolean result from
integer, which yields exactly what you had. I would return boolean instead.
I use the object identifier type
regclass as input type for
_tbl. That does everything
format('%I', _tbl) would do, but better, because:
.. it prevents SQL injection just as well.
.. it fails immediately and more gracefully if the table name is invalid / does not exist / is invisible to the current user. (A
regclassparameter is only applicable for existing tables.)
.. it works with schema-qualified table names, where a plain
format(%I)would fail because they cannot resolve the ambiguity. You would have to pass and escape schema and table names separately.
It only works for existing tables, obviously.
I still use
format(), because it simplifies the syntax (and to demonstrate how it’s used), but with
%s instead of
%I. Typically, queries are more complex so
format() helps more. For the simple example we could as well just concatenate:
EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
No need to table-qualify the
id column while there is only a single table in the
FROM list. No ambiguity possible in this example. (Dynamic) SQL commands inside
EXECUTE have a separate scope, function variables or parameters are not visible there – as opposed to plain SQL commands in the function body.
Here’s why you always escape user input for dynamic SQL properly:
db<>fiddle here demonstrating SQL injection