Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZDEu7-00018Z-UN for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jul 2015 16:42:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZDEu7-00027B-AJ for pgsql-sql@arkaria.postgresql.org; Thu, 09 Jul 2015 16:42:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZDEu5-00025R-Sy for pgsql-sql@postgresql.org; Thu, 09 Jul 2015 16:42:29 +0000 Received: from newmail.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZDEu2-0003FJ-3j for pgsql-sql@postgresql.org; Thu, 09 Jul 2015 16:42:29 +0000 Received: from [127.0.0.1] (unknown [192.168.27.1]) by mail.postgrespro.ru (Postfix) with ESMTPSA id B52DF21C23E9; Thu, 9 Jul 2015 19:42:24 +0300 (MSK) Message-ID: <559EA470.40201@postgrespro.ru> Date: Thu, 09 Jul 2015 19:42:24 +0300 From: Alex Ignatov User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.0 To: "David G. Johnston" CC: "pgsql-sql@postgresql.org" Subject: Re: Strange DOMAIN behavior References: <559E470F.6020002@postgrespro.ru> <559E80E0.8000002@postgrespro.ru> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010408070004060600060609" X-Antivirus: avast! (VPS 150709-1, 09.07.2015), Outbound message X-Antivirus-Status: Clean X-Pg-Spam-Score: -2.2 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------010408070004060600060609 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 09.07.2015 17:23, David G. Johnston wrote: > On Thu, Jul 9, 2015 at 10:10 AM, Alex Ignatov > >wrote: > > 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. > Sorry for top-post. 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. https://www.avast.com/antivirus --------------010408070004060600060609 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 09.07.2015 17:23, David G. Johnston wrote:
On Thu, Jul 9, 2015 at 10:10 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
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.

Sorry for top-post.
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


Avast logo

This email has been checked for viruses by Avast antivirus software.
www.avast.com


--------------010408070004060600060609--