Styjun

Count the number of triangles

Half filled water bottle

What makes these white stars appear black?

Can a network vulnerability be exploited locally?

Employing a contractor proving difficult

If the UK Gov. has authority to cancel article 50 notification, why do they have to agree an extension with the EU

Cutting numbers into a specific decimals

Heat output from a 200W electric radiator?

Number of Fingers for a Math Oriented Race

Why might one *not* want to use a capo?

Group riding etiquette

Why is the Grievance Studies affair considered to be research requiring IRB approval?

The meaning of asynchronous vs synchronous

Why did Lucius make a deal out of Buckbeak hurting Draco but not about Draco being turned into a ferret?

Do multi-engine jets need all engines with equal age to reduce asymmetry in thrust and fuel consumption arising out of deterioration?

Fantasy Macro Economics: What would Merfolk trade for?

In Endgame, wouldn't Stark have remembered Hulk busting out of the stairwell?

Defending Castle from Zombies

Why does AM radio react to IR remote?

Drawing probabilities on a simplex in TikZ

Can I lend a small amount of my own money to a bank at the federal funds rate?

Is the Amazon rainforest the "world's lungs"?

How do Barton (Hawkeye/Ronin) and Romanov (Black Widow) end up on the Benatar on Morag in 2014?

Did anybody find out it was Anakin who blew up the command center?

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

Change type of varchar field to integer: “cannot be cast automatically to type integer”Change column type and set not nullStrange behaviour of Grails' application connected to PostgreSQLUse of custom return types in a FOR loop in plpgsqlNull detection in a complex type with field of another complex typePostgreSQL returns only one row when using custom typesPostgresql - testing RECORD types (for fields or null)How to use variable as field type in plpgsql?Data Type for select in plpgsql function and access its fieldsHow do I overwrite 'not null' fields of a new record on insert using plpgsql?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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 (,,,,,,,,,,,,) 

sql postgresql plpgsql

share|improve this question

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

add a comment | 

0

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 (,,,,,,,,,,,,) 

sql postgresql plpgsql

share|improve this question

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

add a comment | 

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 (,,,,,,,,,,,,) 

sql postgresql plpgsql

share|improve this question

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

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;

NOTICE: found : f NOTICE: payment_method null

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

share|improve this question

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

share|improve this question

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

edited Mar 28 at 8:07

a_horse_with_no_name

330k5252 gold badges514514 silver badges608608 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

asked Mar 27 at 21:26

Sourav MukherjeeSourav Mukherjee

9411 silver badge1212 bronze badges

1 Answer
1

active

oldest

votes

2

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.

share|improve this answer

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55386692%2fcannot-set-custom-type-object-in-plpgsql-to-null-instead-fields-of-that-object%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

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.

share|improve this answer

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

add a comment | 

2

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.

share|improve this answer

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

add a comment | 

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.

share|improve this answer

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

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

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

share|improve this answer

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges

answered Mar 28 at 8:03

Pavel StehulePavel Stehule

25.3k33 gold badges5252 silver badges6262 bronze badges