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()
Or:
SELECT some_f('"my very uncommon table name"');
Major points
Use an 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 EXISTS
to 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 quote_ident(_tbl)
or 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 regclass
parameter is only applicable for existing tables.)
.. it works with schema-qualified table names, where a plain quote_ident(_tbl)
or 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
Old sqlfiddle