CODEWITHSUNDEEP
postgresqlplpgsql
Cataclysm
Cataclysm

Reputation: 8548

How to initialize row datatype variables?

I would like to create a function that initialize and return the row datatype of a table as

CREATE FUNCTION get_default_table_row_object() 
RETURNS mytable AS $$
    DECLARE 
    row mytable;
    BEGIN
        row.field1 := 0;
        row.field2 := -1;
        row.record_reg_id := 1;
        row.record_upd_id := 1;
        row.record_reg_date := current_timestamp;
        row.record_upd_date := current_timestamp;

        RETURN row;
    END;
$$ LANGUAGE plpgsql;

becuase my table has alot of columns and I need to create dozens of variables at several functions. I would like to use above function as

CREATE FUNCTION some_function() RETURNS VOID AS $$
DECLARE    
    i_obj1 mytable := get_default_table_row_object(); -- declare and initialize default values
BEGIN
    -- function body
END;
$$ LANGUAGE plpgsql;

But this give me the error ERROR: default value for row or record variable is not supported. Has someway to figure it out ?

Upvotes: 0

Views: 1038

Answers (1)

Nick
Nick

Reputation: 7431

You can set it in the body instead, like so:

CREATE FUNCTION some_function() RETURNS VOID AS $$
DECLARE    
    i_obj1 mytable; -- declare only
BEGIN
    i_obj1 := get_default_table_row_object(); -- set default values
END;
$$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions