CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Sourav Mukherjee
Sourav Mukherjee

Reputation: 309

Cannot set custom type object in plpgsql to null. Instead fields of that object becomes null

I am trying to attach null to a custom type object to null. But only the fields inside that object becomes null instead of the object itself.

I have tried using the FOUND flag to determine whether any row was assigned to my object. If found was false, then the custom type object is set to null. But when I print the object, in pgadmin console it looks like (,,,,,,). 

Select id,provider_id,beneficiary_name,beneficiary_branch,beneficiary_account_number,swift_code,
    payment_terms,currency,bank_name,payment_entity,payment_type,invoice_due_date_type,last_updated_time
    INTO provider_payment_method_result
    from provider_info.provider_payment_method where provider_id = provider_id_param;
    IF NOT FOUND
       THEN provider_payment_method_result := NULL::provider_info_api.payment_method;
    END IF;
    raise notice 'found : %', found;
    raise notice 'payment_method % ', provider_payment_method_result;
    return provider_payment_method_result;

I expect 

NOTICE: found : f NOTICE: payment_method null

Actual result

NOTICE: found : f NOTICE: payment_method (,,,,,,,,,,,,)

Upvotes: 0

Views: 231

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45835

This depends on version of PostgreSQL. Where PostgreSQL is older than 10, then behave described by you is expected. Row (or composite value) with NULL in all fields is effectively NULL. On these version, the composite value is passed by value every time (from SQL perspective) and cannot be expressed as one value (although this value is NULL).

On PostgreSQL 10 and higher this behave is changed.

CREATE TYPE xy AS (x int, y int);

DO $$
DECLARE _xy xy;
BEGIN
  _xy := (NULL, NULL);
  RAISE NOTICE '% %', _xy, _xy IS NULL;
END;
$$;

If you have older Postgres, and you would to see just NULL, use RECORD type instead.

Upvotes: 3

Related Questions