CODEWITHSUNDEEP
postgresqlnullinitializationplpgsqlbulk
TheLovelySausage
TheLovelySausage

Reputation: 4094

Reset all columns of a row type to NULL

I was wondering if it's possible to bulk initialize variables that are created from a type to null without initializing them each separately

create type tp_data as (
    data01   text,
    data02   integer,
    data03   text
);

create function sp_function()
  returns setof tp_data as
$$
declare
    lr_data   tp_data;
begin
    for lr_data in
        select data.data01, data.data02, data.data03
        from data
    loop
        if lr_data.data01 = "cancelled" then

            -- what is the correct way to do this?
            lr_data.* := null;

            -- without having to do this
            lr_data.data01 := null;
            lr_data.data02 := null;
            lr_data.data03 := null;
        end if;

        return next lr_data;
    end loop;
end
$$
language plpgsql;

Call:

select * from sp_function();

The above is clearly wrong however in Informix I am able to use something like "initialize lr_data.* to null". Is there a way to do something similar in plpgsql?

Upvotes: 4

Views: 10517

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657052

You can just assign NULL to the whole row type:

lr_data := null;

This sets every single column to NULL.

Aside: there are a couple of syntax errors in your function. And the whole function could be radically simplified. This query replaces the whole function:

SELECT (CASE WHEN data01 = 'cancelled' THEN NULL::tp_data ELSE d::tp_data END).*
FROM (SELECT data01, data02, data03 FROM data) d;

Not the point of your question, I know. But it demonstrates that we can also cast a NULL value to the row type to make all columns NULL.

Upvotes: 6

Related Questions