samedi 14 mars 2015

INSERT inside PL/pgSQL function gives different result

In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function argument and using the exact same query, I get an unexpected result:



CREATE TYPE my_test_type as (part1 text, part2 text);
CREATE TABLE my_test_table (id serial, test_col my_test_type);
CREATE FUNCTION my_test_table_insert (test_arg my_test_type DEFAULT NULL)
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO my_test_table (test_col) VALUES (test_arg);
END; $$;

-- gives null as expected
SELECT NULL::my_test_type;

-- test_col value will be null as expected
INSERT INTO my_test_table (test_col) VALUES (NULL);

-- this performs the exact same query above but in a function,
-- but test_col value shows '(,)', which is NOT expected
SELECT my_test_table_insert(NULL);

-- this is what seems to happening in the function, but I don't understand why
INSERT INTO my_test_table(test_col) VALUES (ROW(NULL, NULL)::my_test_type);

\pset null 'NULL'
SELECT *, (test_col).part1, (test_col).part2, test_col IS NULL AS is_null FROM my_test_table;
┌────┬──────────┬───────┬───────┬─────────┐
│ id │ test_col │ part1 │ part2 │ is_null │
├────┼──────────┼───────┼───────┼─────────┤
│ 1 │ NULL │ NULL │ NULL │ t │
│ 2 │ (,) │ NULL │ NULL │ t │
│ 3 │ (,) │ NULL │ NULL │ t │
└────┴──────────┴───────┴───────┴─────────┘
(3 rows)


Even though everything tests as null, why does the result from the INSERT inside the function show differently in the table?


Edit: The problem can be simplified:



VALUES (NULL::my_test_type), (ROW(NULL, NULL)::my_test_type);
┌─────────┐
│ column1 │
├─────────┤
│ NULL │
│ (,) │
└─────────┘
(2 rows)

Aucun commentaire:

Enregistrer un commentaire