Re: Strange DOMAIN behavior - Mailing list pgsql-sql

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

---

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