waiting for pg 8.4 – select * from depesz;

on sunday, 23rd of march, tom lane commited another new-feature patch. this new patch was from brendan jurd, and adds quote_nullable() function:

commit message:

Log Message:
-----------
CREATE a FUNCTION quote_nullable(), which works the same AS quote_literal()
EXCEPT that it RETURNS the string 'NULL', rather than a SQL NULL, WHEN called
WITH a NULL argument.  This IS often a much more useful behavior FOR
constructing dynamic queries.  ADD more discussion TO the documentation
about how TO USE these functions.

what is this for?

well, imagine you have been building some kind of dynamic sql in your plpgsql function/trigger.

it was something like:

CREATE OR REPLACE FUNCTION test_it(TABLE_NAME TEXT, column_name TEXT, new_value TEXT, use_id INT4) RETURNS void AS $BODY$
DECLARE
SQL TEXT;
BEGIN
    SQL := 'UPDATE ' || quote_ident(TABLE_NAME) || ' SET ' || quote_ident(column_name) || ' = ' || quote_literal(new_value) || ' WHERE id = ' || use_id::TEXT;
    raise notice '[%]', SQL;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;

which works like this:

# SELECT test_it('test_table', 'test_column', 'some_value', 123);
NOTICE:  [UPDATE test_table SET test_column = 'some_value' WHERE id = 123]

but, what will happen if i want to test “test_column" to null?

# SELECT test_it('test_table', 'test_column', NULL, 123);
NOTICE:  [<NULL>]

oops.

this is because quote_literal(NULL) is null.

this new function – quote_nullable() returns “NULL" string. so let's modify test function:

CREATE OR REPLACE FUNCTION test_it(TABLE_NAME TEXT, column_name TEXT, new_value TEXT, use_id INT4) RETURNS void AS $BODY$
DECLARE
SQL TEXT;
BEGIN
    SQL := 'UPDATE ' || quote_ident(TABLE_NAME) || ' SET ' || quote_ident(column_name) || ' = ' || quote_nullable(new_value) || ' WHERE id = ' || use_id::TEXT;
    raise notice '[%]', SQL;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;

and now it works like this:

# SELECT test_it('test_table', 'test_column', NULL, 123);
NOTICE:  [UPDATE test_table SET test_column = NULL WHERE id = 123]

which is much better 🙂

of course – even without quote_nullable() you could do the same thing with coalesce (COALESCE(quote_literal(new_value), ‘NULL'), but this new function makes it much easier and more readable.