Get / Set Composite Type Fields within triggers
Hi
This is my first question, so, forgive me if it's a newby issue but I
couldn't find an answer googling.
I have a simple composite type
CREATE TYPE info_base AS
(by text,
at timestamp without time zone);
I want to use it as the data type of created, modified and deleted fields.
I already know hot to index, update and select a sub-field like
CREATE INDEX ix_created_by ON xxx.yyy ((created).by);
UPDATE xxx.yyy SET created.by = 'user';
select * from xxx.yyy where (created).by = 'that user';
My problem is inside triggers: How can I set or get type field values for
NEW or OLD? (I need it to check users permissions at row level, etc.)
I tried NEW.((created).by), NEW.(created.by), NEW.created.by and nothing
works...
Could you help me please?
Thanks in advance.
Martin
My problem is inside triggers: How can I set or get type field values for
NEW or OLD? (I need it to check users permissions at row level, etc.)I tried NEW.((created).by), NEW.(created.by), NEW.created.by and nothing
works...Could you help me please?
Thanks in advance.
Martin
for accessing you could use
(NEW.created).by
for setting it, as far as I know it's not possible to assign directly
to a field of a composite type in a record,
but something like this would work
# create table foo (a info_base);
# create or replace function foobartrig() returns trigger AS
$func$
declare rec info_base;
begin
rec := ('AAAAAAAAAAAAAAAAAA', (NEW.a).at);
NEW.a := rec;
raise warning '%', NEW;
RETURN NEW;
END;
$func$
language plpgsql;
CREATE FUNCTION
# insert into foobar values (('aoeu',current_timestamp)::info_base)
;
WARNING: ("(AAAAAAAAAAAAAAAAAA,""2013-06-24 11:55:55.610049"")")
Hope this was of some help...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general