Обсуждение: pl/pgsql RECORD data type, how to access to the values
Hello,
I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as argument to the trigger function.
Provided my table has only one column named 'id', I can do easilly
CREATE FUNCTION ft() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'It works:%', OLD.id;
END
$$ LANGUAGE plpgsql;
But I'd like to do
CREATE FUNCTION ft() RETURNS trigger AS $$
DECLARE
col VARCHAR;
BEGIN
col = TG_ARGV[0]
RAISE NOTICE 'This does not works:%', OLD.col
RAISE NOTICE 'This also does not works:%', OLD[col]
END
$$ LANGUAGE plpgsql;
I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I checked the docs.
I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as argument to the trigger function.
Provided my table has only one column named 'id', I can do easilly
CREATE FUNCTION ft() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'It works:%', OLD.id;
END
$$ LANGUAGE plpgsql;
But I'd like to do
CREATE FUNCTION ft() RETURNS trigger AS $$
DECLARE
col VARCHAR;
BEGIN
col = TG_ARGV[0]
RAISE NOTICE 'This does not works:%', OLD.col
RAISE NOTICE 'This also does not works:%', OLD[col]
END
$$ LANGUAGE plpgsql;
I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I checked the docs.
Hello On 05/04/2008, Guillaume Bog <guibog@gmail.com> wrote: > Hello, > > I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as > argument to the trigger function. > > Provided my table has only one column named 'id', I can do easilly > > CREATE FUNCTION ft() RETURNS trigger AS $$ > BEGIN > RAISE NOTICE 'It works:%', OLD.id; > END > $$ LANGUAGE plpgsql; > > But I'd like to do > > CREATE FUNCTION ft() RETURNS trigger AS $$ > DECLARE > col VARCHAR; > BEGIN > col = TG_ARGV[0] > RAISE NOTICE 'This does not works:%', OLD.col > RAISE NOTICE 'This also does not works:%', OLD[col] > END > $$ LANGUAGE plpgsql; > > I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I > checked the docs. It's not possible in plpgsql. You have to use plperl, pltcl or plpython. Regards Pavel Stehule > >
On Sat, Apr 5, 2008 at 4:05 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > > > > On 05/04/2008, Guillaume Bog <guibog@gmail.com> wrote: > > Hello, > > > > I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as > > argument to the trigger function. > > > > Provided my table has only one column named 'id', I can do easilly > > > > CREATE FUNCTION ft() RETURNS trigger AS $$ > > BEGIN > > RAISE NOTICE 'It works:%', OLD.id; > > END > > $$ LANGUAGE plpgsql; > > > > But I'd like to do > > > > CREATE FUNCTION ft() RETURNS trigger AS $$ > > DECLARE > > col VARCHAR; > > BEGIN > > col = TG_ARGV[0] > > RAISE NOTICE 'This does not works:%', OLD.col > > RAISE NOTICE 'This also does not works:%', OLD[col] > > END > > $$ LANGUAGE plpgsql; > > > > I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I > > checked the docs. > > It's not possible in plpgsql. You have to use plperl, pltcl or plpython. Ok, thanks. I may keep my code in the previous state instead because I only have a little bit of duplication that currently still fits on one screen, and it seems preferable to use pl/pgsql in my case. Another question that is puzzling me: I want a table to be "read-only", so I raise exceptions with a before trigger on update, insert and delete. It works well. This read-only table is modified (delete + insert) by a trigger function set on another table, but this second trigger calls the first and I can modify my read-only table. I'd like my "read-only" trigger to be aware that the modification call on the read-only table comes from the second trigger. Is it possible? I have read in some places that I should use a rule instead, but I never used them and it seems complex. I would prefer not to set up complex access rules with GRANT and REVOKE because my access rules in simple and works now. The best solution I can think of so far is to have the client application work with a view, but having this behavior fully managed through triggers would be more natural and I fear I missed something in the docs. > Regards > Pavel Stehule > > > > > >
> > This read-only table is modified (delete + insert) by a trigger > function set on another table, but this second trigger calls the first > and I can modify my read-only table. I'd like my "read-only" trigger > to be aware that the modification call on the read-only table comes > from the second trigger. Is it possible? PostgreSQL call triggers in alphabet order > > I have read in some places that I should use a rule instead, but I > never used them and it seems complex. I would prefer not to set up > complex access rules with GRANT and REVOKE because my access rules in > simple and works now. The best solution I can think of so far is to > have the client application work with a view, but having this behavior > fully managed through triggers would be more natural and I fear I > missed something in the docs. > it's depend on application Pavel > > Regards > > Pavel Stehule > > > > > > > > > > >
Guillaume Bog wrote: > I want a table to be "read-only", so I raise exceptions with a before > trigger on update, insert and delete. It works well. > > This read-only table is modified (delete + insert) by a trigger > function set on another table, but this second trigger calls the first > and I can modify my read-only table. I'd like my "read-only" trigger > to be aware that the modification call on the read-only table comes > from the second trigger. Is it possible? Quick question: Why not make the read only table a view of the writeable table, instead of using triggers to copy data? If your data doesn't fit that use or that'd be inefficient, can you use access privileges rather than a trigger to limit changes to the read only table? I find that limiting a user to SELECT priveleges on a table and using a SECURITY DEFINER trigger or other function to perform certain restricted priveleged operations on the table to be very useful. In your case you might be able to restrict users to SELECT priveleges on your read only table, drop the "read only" restriction trigger, and make the updating trigger SECURITY DEFINER (after carefully thinking about possible risks and issues). Why the separate read only table, anyway? A materialized view / summary table? Something to do with user access control ? -- Craig Ringer
On Mon, Apr 7, 2008 at 1:56 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Guillaume Bog wrote: > > > I want a table to be "read-only", so I raise exceptions with a before > > trigger on update, insert and delete. It works well. > > > > This read-only table is modified (delete + insert) by a trigger > > function set on another table, but this second trigger calls the first > > and I can modify my read-only table. I'd like my "read-only" trigger > > to be aware that the modification call on the read-only table comes > > from the second trigger. Is it possible? > > Quick question: Why not make the read only table a view of the writeable > table, instead of using triggers to copy data? That's how it is now. I have a writable table of events on objects, and a (complex) view depicting the status of my objects according to those events. I have pushed a lot of logic on SQL side, mostly with views, and I'm very happy with this design choice so far (I can completely change my core logic in few hours!), but the drawback is that selecting lists of objects according to their states becomes slower (one second or more). So I tried to materialize one of the status view, and it seems to work well, but I'd just want to make sure nobody alters it. It seems that using privileges and SECURITY DEFINER will be the best approach to protect data integrity. I have a related issue with a value very often accessed by my object, and available only in the "grand grand-parents", which makes a triple join very common in many statements. I hid the triple join behind a view for convenience but the performance is so-so (even with indexes on each foreign keys), and I want to try to denormalize this value, and duplicate it in the object. So here also I need triggers, and will try the SECURITY DEFINER option (while it is different, because I need to make only one column "read-only", not a full table) Thanks for the tip. > If your data doesn't fit that use or that'd be inefficient, can you use > access privileges rather than a trigger to limit changes to the read > only table? I find that limiting a user to SELECT priveleges on a table > and using a SECURITY DEFINER trigger or other function to perform > certain restricted priveleged operations on the table to be very useful. > In your case you might be able to restrict users to SELECT priveleges on > your read only table, drop the "read only" restriction trigger, and make > the updating trigger SECURITY DEFINER (after carefully thinking about > possible risks and issues). > > Why the separate read only table, anyway? A materialized view / summary > table? Something to do with user access control ? > > -- > Craig Ringer >