Обсуждение: Triggers and Domains

Hello,
I have a DOMAIN LastUpdateUser and I want to use it in all the tables of the
database. I want also to create a trigger which will fulfill this domain
with the user name.
Is it possible to do it on Domain instead of the table?

Thanks
--
View this message in context: http://www.nabble.com/Triggers-and-Domains-tp25209238p25209238.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

ekekakos <ekekakos@hol.gr> writes:
> I have a DOMAIN LastUpdateUser and I want to use it in all the tables of the
> database. I want also to create a trigger which will fulfill this domain
> with the user name.
> Is it possible to do it on Domain instead of the table?

No.  But wouldn't a DEFAULT expression attached to the domain serve just
as well?

            regards, tom lane

Thanks but how, in SQL server you put the function USER() (if I remember
well) and it works.
Here it does not work. I must put in all of my table the 2 fields
LASTUPDATEUSER and LASTUPDATEDATETIME and I am trying to find a quick way. I
thought the inheritance but I do not think that it will work.
I will be much appreciate if someone can help me on this.

Thanks
Elias


Tom Lane-2 wrote:
>
> ekekakos <ekekakos@hol.gr> writes:
>> I have a DOMAIN LastUpdateUser and I want to use it in all the tables of
>> the
>> database. I want also to create a trigger which will fulfill this domain
>> with the user name.
>> Is it possible to do it on Domain instead of the table?
>
> No.  But wouldn't a DEFAULT expression attached to the domain serve just
> as well?
>
>             regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

--
View this message in context: http://www.nabble.com/Triggers-and-Domains-tp25209238p25213142.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

ekekakos <ekekakos@hol.gr> writes:
> Thanks but how, in SQL server you put the function USER() (if I remember
> well) and it works.
> Here it does not work.

Define "does not work", please?

regression=# create domain username as text default user;
CREATE DOMAIN
regression=# create table foo(f1 int, lastupdateuser username);
CREATE TABLE
regression=# insert into foo(f1) values(42);
INSERT 0 1
regression=# select * from foo;
 f1 | lastupdateuser
----+----------------
 42 | postgres
(1 row)


            regards, tom lane

It does not work.
I run your script
create domain username as text default user;
through PGADMIN and I get the following
CREATE DOMAIN username
  AS text
  DEFAULT "current_user"();
ALTER DOMAIN username OWNER TO ekekakos;

I create a field in a table and I am getting the following script:
ALTER TABLE "Library"."Contacts" ADD COLUMN "LastUser" username;
ALTER TABLE "Library"."Contacts" ALTER COLUMN "LastUser" SET STORAGE
EXTENDED;

And it does not work during the update.

Thanks
Elias


Tom Lane-2 wrote:
>
> ekekakos <ekekakos@hol.gr> writes:
>> Thanks but how, in SQL server you put the function USER() (if I remember
>> well) and it works.
>> Here it does not work.
>
> Define "does not work", please?
>
> regression=# create domain username as text default user;
> CREATE DOMAIN
> regression=# create table foo(f1 int, lastupdateuser username);
> CREATE TABLE
> regression=# insert into foo(f1) values(42);
> INSERT 0 1
> regression=# select * from foo;
>  f1 | lastupdateuser
> ----+----------------
>  42 | postgres
> (1 row)
>
>
>             regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

--
View this message in context: http://www.nabble.com/Triggers-and-Domains-tp25209238p25214251.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

ekekakos <ekekakos@hol.gr> writes:
> And it does not work during the update.

Oh, you mean you want the field to change during updates?  No, a
DEFAULT won't do that for you.  You'll have to use a trigger.

            regards, tom lane

So for every table I must create the 2 fields with the 2 triggers. Is there
any way to simplify this?
From what you are saying I think no. Am I right?

Thanks again
Elias


Tom Lane-2 wrote:
>
> ekekakos <ekekakos@hol.gr> writes:
>> And it does not work during the update.
>
> Oh, you mean you want the field to change during updates?  No, a
> DEFAULT won't do that for you.  You'll have to use a trigger.
>
>             regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

--
View this message in context: http://www.nabble.com/Triggers-and-Domains-tp25209238p25214530.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.