Thread: Strange DOMAIN behavior
Hello everyone!!! Got strange DOMAIN behavior in the following plpgsql code: postgres=# DROP DOMAIN lexema_str CASCADE; DROP DOMAIN postgres=# CREATE DOMAIN lexema_str TEXT DEFAULT 'abc'; CREATE DOMAIN postgres=# DO $$ postgres$# DECLARE postgres$# lex lexema_str; postgres$# BEGIN postgres$# RAISE NOTICE 'lex = %', lex; postgres$# END; postgres$# $$; NOTICE: lex = <NULL> DO But i expect that lex = abc! So default value of DOMAIN type is not set in pgplsql block but: postgres=# DROP DOMAIN lexema_str CASCADE; DROP DOMAIN postgres=# CREATE DOMAIN lexema_str TEXT DEFAULT 'abc' NOT NULL; CREATE DOMAIN postgres=# DO $$ postgres$# DECLARE postgres$# lex lexema_str; postgres$# BEGIN postgres$# RAISE NOTICE 'lex = %', lex; postgres$# END; postgres$# $$; ERROR: domain lexema_str does not allow null values CONTEXT: PL/pgSQL function inline_code_block line 4 during statement block local variable initialization So constraints in DOMAIN type work in pgplsql ! Is this correct behavior?? -- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
On Thursday, July 9, 2015, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
Hello everyone!!!
Got strange DOMAIN behavior in the following plpgsql code:
But i expect that lex = abc!
So default value of DOMAIN type is not set in pgplsql block but:
Is this correct behavior??
If you read the create domain sql command documentation carefully the default clause is only used when inserting into a table that has a column of the domain type that is not explicitly provided a value. Each language deals with domains differently and the behavior you expect is not currently implemented in pl/pgsql. If you want a default inside the procedure you need to declare one explicitly.
David J.
Thank you for your reply.
In fact i want to use domains in the following case:
DROP DOMAIN lexema_str CASCADE;
CREATE DOMAIN lexema_str TEXT DEFAULT 'abc' NOT NULL;
DROP TYPE lexema_test CASCADE;
CREATE TYPE lexema_test AS (
lex lexema_str,
lex2 BIGINT
);
DROP TABLE ttt;
CREATE TABLE ttt (
a lexema_test,
b BIGINT
);
INSERT INTO ttt (b) VALUES (1);
SELECT *
FROM ttt;
a | b
---+---
| 1
(1 row)
a.lex is null again not 'abc' as I expected like in plpgsql.
All i want is to have default values in composite types. Feature that Oracle have.
I thought that with domain type it should be possible.
In fact i want to use domains in the following case:
DROP DOMAIN lexema_str CASCADE;
CREATE DOMAIN lexema_str TEXT DEFAULT 'abc' NOT NULL;
DROP TYPE lexema_test CASCADE;
CREATE TYPE lexema_test AS (
lex lexema_str,
lex2 BIGINT
);
DROP TABLE ttt;
CREATE TABLE ttt (
a lexema_test,
b BIGINT
);
INSERT INTO ttt (b) VALUES (1);
SELECT *
FROM ttt;
a | b
---+---
| 1
(1 row)
a.lex is null again not 'abc' as I expected like in plpgsql.
All i want is to have default values in composite types. Feature that Oracle have.
I thought that with domain type it should be possible.
On 09.07.2015 15:49, David G. Johnston wrote:
On Thursday, July 9, 2015, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:Hello everyone!!!
Got strange DOMAIN behavior in the following plpgsql code:
But i expect that lex = abc!
So default value of DOMAIN type is not set in pgplsql block but:
Is this correct behavior??If you read the create domain sql command documentation carefully the default clause is only used when inserting into a table that has a column of the domain type that is not explicitly provided a value. Each language deals with domains differently and the behavior you expect is not currently implemented in pl/pgsql. If you want a default inside the procedure you need to declare one explicitly.David J.
-- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
![]() | This email has been checked for viruses by Avast antivirus software. |
Thank you for your reply.
In fact i want to use domains in the following case:
DROP DOMAIN lexema_str CASCADE;
CREATE DOMAIN lexema_str TEXT DEFAULT 'abc' NOT NULL;
DROP TYPE lexema_test CASCADE;
CREATE TYPE lexema_test AS (
lex lexema_str,
lex2 BIGINT
);
DROP TABLE ttt;
CREATE TABLE ttt (
a lexema_test,
b BIGINT
);
INSERT INTO ttt (b) VALUES (1);
SELECT *
FROM ttt;
a | b
---+---
| 1
(1 row)
a.lex is null again not 'abc' as I expected like in plpgsql.
All i want is to have default values in composite types. Feature that Oracle have.
I thought that with domain type it should be possible.
Please don't top-post.
So even though there is no default specified for column a you expect there to be a non-null value even though the column was omitted from the insert statement? I doubt that such a change in behavior would be accepted.
As far as I know the less-redundant way to accomplish your goal is to create a constructor function for the type (CREATE FUNCTION default_type() RETURNS type) and call it where you need to interject a default.
CREATE TABLE ttt ( a lexema_test DEFAULT ROW(default_type(), NULL)::lexema_test )
There likely isn't any hard reasons behind the lack of capabilities wrt. defaults and composites/domains; its just that no one has been bothered enough to affect change.
David J.
On 09.07.2015 17:23, David G. Johnston wrote:
Sorry for top-post.Thank you for your reply.
In fact i want to use domains in the following case:
DROP DOMAIN lexema_str CASCADE;
CREATE DOMAIN lexema_str TEXT DEFAULT 'abc' NOT NULL;
DROP TYPE lexema_test CASCADE;
CREATE TYPE lexema_test AS (
lex lexema_str,
lex2 BIGINT
);
DROP TABLE ttt;
CREATE TABLE ttt (
a lexema_test,
b BIGINT
);
INSERT INTO ttt (b) VALUES (1);
SELECT *
FROM ttt;
a | b
---+---
| 1
(1 row)
a.lex is null again not 'abc' as I expected like in plpgsql.
All i want is to have default values in composite types. Feature that Oracle have.
I thought that with domain type it should be possible.Please don't top-post.So even though there is no default specified for column a you expect there to be a non-null value even though the column was omitted from the insert statement? I doubt that such a change in behavior would be accepted.As far as I know the less-redundant way to accomplish your goal is to create a constructor function for the type (CREATE FUNCTION default_type() RETURNS type) and call it where you need to interject a default.CREATE TABLE ttt ( a lexema_test DEFAULT ROW(default_type(), NULL)::lexema_test )There likely isn't any hard reasons behind the lack of capabilities wrt. defaults and composites/domains; its just that no one has been bothered enough to affect change.David J.
It is sad but constructor function for composite type doesn't work in declare block. For example:
CREATE OR REPLACE FUNCTION new_lexema()
RETURNS lexema AS $body$
DECLARE
lex lexema;
BEGIN
lex=ROW ('tt',0);
RETURN lex;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER;
DROP FUNCTION lexema_test( );
CREATE OR REPLACE FUNCTION lexema_test()
RETURNS VOID AS $body$
DECLARE
lex lexema :=new_lexema();
BEGIN
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER;
Then I got:
ERROR: default value for row or record variable is not supported
LINE 17: lex lexema :=new_lexema();
-- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
![]() | This email has been checked for viruses by Avast antivirus software. |
CREATE OR REPLACE FUNCTION lexema_test()DROP FUNCTION lexema_test( );
RETURNS VOID AS $body$
DECLARE
lex lexema :=new_lexema();
BEGIN
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER;
Then I got:
ERROR: default value for row or record variable is not supported
LINE 17: lex lexema :=new_lexema();
Undocumented limitation :(
While slightly more verbose you simply need to:
DECLARE lex lexema;
BEGIN
lex := new_lexema();
[...]
David J.
On 09.07.2015 19:50, David G. Johnston wrote:
Thats sad =(. But it is not so gracefully to initialize composite type in the begin block as it would be in declare block %).CREATE OR REPLACE FUNCTION lexema_test()DROP FUNCTION lexema_test( );
RETURNS VOID AS $body$
DECLARE
lex lexema :=new_lexema();
BEGIN
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER;
Then I got:
ERROR: default value for row or record variable is not supported
LINE 17: lex lexema :=new_lexema();
Undocumented limitation :(While slightly more verbose you simply need to:DECLARE lex lexema;BEGINlex := new_lexema();[...]David J.
Thank you for yours answers!
-- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
![]() | This email has been checked for viruses by Avast antivirus software. |