Обсуждение: Strange DOMAIN behavior
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. |
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. |
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();
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. |