After dealing with quoting and basic security (SQL injection) in general, I want to shift your focus to another important topic: scopes.
Just like most popular programming languages I am aware of, PL/pgSQL uses variables depending on their context. Variables are defined in the DECLARE statement of a function. However, PL/pgSQL allows you to nest a DECLARE statement:
CREATE FUNCTION scope_test () RETURNS int AS $$ DECLARE i int := 0; BEGIN RAISE NOTICE 'i1: %', i; DECLARE i int; BEGIN RAISE NOTICE 'i2: %', i; END; RETURN i; END; $$ LANGUAGE 'plpgsql';
In the DECLARE statement, a variable i is defined and a value is assigned to it. Then, i is displayed. The output will, of course, be 0. Then, a second DECLARE statement starts. ...