SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Modern PostgreSQL format() has a built-in way to escape identifiers. Simpler than before: CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘INSERT INTO %I.%I SELECT $1.*’ , TG_TABLE_SCHEMA, TG_TABLE_NAME || ‘shadow’) USING OLD; RETURN OLD; END $func$; Works with a VALUES expression as well. db<>fiddle here Old sqlfiddle Major points … Read more
These dollar signs ($$) are used for dollar quoting, which is in no way specific to function definitions. It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts. The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to … Read more
It’s the recurring problem of SELECT or INSERT under possible concurrent write load, related to (but different from) UPSERT (which is INSERT or UPDATE). This PL/pgSQL function uses UPSERT (INSERT … ON CONFLICT .. DO UPDATE) to INSERT or SELECT a single row: CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) LANGUAGE plpgsql AS … Read more
Dynamic SQL and RETURN type (I saved the best for last, keep reading!) You want to execute dynamic SQL. In principal, that’s simple in plpgsql with the help of EXECUTE. You don’t need a cursor. In fact, most of the time you are better off without explicit cursors. The problem you run into: you want … Read more
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 … Read more