Обсуждение: Emulating trigger BEFORE SELECT behavior
Hi all, I need a tool which allows me to do a task before every SELECT on a table. Specifically,the behavior I would get with a BEFORE SELECT trigger. Please advice me on this. Regards, Atri -- Regards, Atri l'apprenant
Hi Atri Maybe you could think different and, instead of do a "before select trigger", you can: - create a store procedure with result is a recordset - create a view If you can't... could you please explain us a bit more about the requirements about this before action? Good luck Regards El 18/04/2013, a las 08:54, Atri Sharma <atri.jiit@gmail.com> escribió: > Hi all, > > I need a tool which allows me to do a task before every SELECT on a > table. Specifically,the behavior I would get with a BEFORE SELECT > trigger. > > Please advice me on this. > > Regards, > > Atri > > -- > Regards, > > Atri > l'apprenant > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Alfonso Afonso (personal)
On Thu, Apr 18, 2013 at 5:35 PM, Alfonso Afonso <aafonsoc@gmail.com> wrote: > Hi Atri > > Maybe you could think different and, instead of do a "before select trigger", you can: > > - create a store procedure with result is a recordset > - create a view > > If you can't... could you please explain us a bit more about the requirements about this before action? > > Good luck Hi Alonso, Sure, I will try that out. The before operation shall get the values of all the columns of the queried table, convert them to a string and insert the string into a different table.Then, the SELECT query shall proceed as before. Regards, Atri -- Regards, Atri l'apprenant
On 04/18/2013 05:12 AM, Atri Sharma wrote: > On Thu, Apr 18, 2013 at 5:35 PM, Alfonso Afonso <aafonsoc@gmail.com> wrote: >> Hi Atri >> >> Maybe you could think different and, instead of do a "before select trigger", you can: >> >> - create a store procedure with result is a recordset >> - create a view >> >> If you can't... could you please explain us a bit more about the requirements about this before action? >> >> Good luck > > Hi Alonso, > > Sure, I will try that out. > > The before operation shall get the values of all the columns of the > queried table, convert them to a string and insert the string into a > different table.Then, the SELECT query shall proceed as before. How about a RULE: http://www.postgresql.org/docs/9.2/interactive/sql-createrule.html Rules can be tricky, so I would at least skim through: http://www.postgresql.org/docs/9.2/interactive/rules.html > > Regards, > > Atri > > -- > Regards, > > Atri > l'apprenant > > -- Adrian Klaver adrian.klaver@gmail.com
> How about a RULE: > > http://www.postgresql.org/docs/9.2/interactive/sql-createrule.html > > Rules can be tricky, so I would at least skim through: > > http://www.postgresql.org/docs/9.2/interactive/rules.html Thanks. It looks like that it is another way to create a view, which is probably not I want(I need to store some data per SELECT query in another table). One way I was thinking of was creating an updatable view, which is initialized to NULL. As SELECT queries take place, I can update the view to include the new rows. What say? Atri -- Regards, Atri l'apprenant
On 04/18/2013 07:02 AM, Atri Sharma wrote: >> How about a RULE: >> >> http://www.postgresql.org/docs/9.2/interactive/sql-createrule.html >> >> Rules can be tricky, so I would at least skim through: >> >> http://www.postgresql.org/docs/9.2/interactive/rules.html > > Thanks. > > It looks like that it is another way to create a view, which is > probably not I want(I need to store some data per SELECT query in > another table). My mistake, forgot ON SELECT only supports DO INSTEAD. > > One way I was thinking of was creating an updatable view, which is > initialized to NULL. As SELECT queries take place, I can update the > view to include the new rows. > > What say? So what would you run the SELECT against, another view or table? > > Atri > > > > -- > Regards, > > Atri > l'apprenant > > -- Adrian Klaver adrian.klaver@gmail.com
> > So what would you run the SELECT against, another view or table? No, what I meant was: SELECT on main table: fires a rule which updates a view V1 Now, essentially, view V1 has the data I was trying to acquire originally through BEFORE INSERT trigger. When I need the data, I can query view V1. Atri -- Regards, Atri l'apprenant
On 04/18/2013 07:19 AM, Atri Sharma wrote: >> >> So what would you run the SELECT against, another view or table? > > No, what I meant was: > > SELECT on main table: fires a rule which updates a view V1 > > Now, essentially, view V1 has the data I was trying to acquire > originally through BEFORE INSERT trigger. > > When I need the data, I can query view V1. Fair warning, it is morning here, the body is up, the brain cells are lagging behind:) So here it goes, from the docs: "Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT command" So if you want to do an UPDATE I'm guessing you will need to create a function for the SELECT. Basically Alfonso's previous suggestion. This is where things can start getting tricky. This is where I grab a pencil and start laying out ideas on paper. Right now I cannot offer anything more than that. > > Atri > > > -- > Regards, > > Atri > l'apprenant > > -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Apr 18, 2013 at 11:02 AM, Atri Sharma <atri.jiit@gmail.com> wrote:
[...]
One way I was thinking of was creating an updatable view, which is
initialized to NULL. As SELECT queries take place, I can update the
view to include the new rows.
Why you just create your track function and a view to call it?
BEGIN;
CREATE TABLE foo (id SERIAL PRIMARY KEY, data TEXT);
CREATE TABLE foo_track(tracktime TIMESTAMP DEFAULT now(), foo_row foo);
INSERT INTO foo (data) SELECT 'Some Data'||id FROM generate_series(1,10) AS id;
CREATE OR REPLACE FUNCTION foo_track_func(foo) RETURNS integer AS
$$
INSERT INTO foo_track(foo_row) VALUES ($1) RETURNING (foo_row).id
$$
LANGUAGE sql;
CREATE VIEW v_foo AS SELECT foo.*, foo_track_func(foo.*) FROM foo;
SELECT * FROM v_foo;
SELECT * FROM foo_track;
COMMIT;
Regards,
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
> Why you just create your track function and a view to call it? > > Example: > > BEGIN; > > CREATE TABLE foo (id SERIAL PRIMARY KEY, data TEXT); > CREATE TABLE foo_track(tracktime TIMESTAMP DEFAULT now(), foo_row foo); > > INSERT INTO foo (data) SELECT 'Some Data'||id FROM generate_series(1,10) AS > id; > > CREATE OR REPLACE FUNCTION foo_track_func(foo) RETURNS integer AS > $$ > INSERT INTO foo_track(foo_row) VALUES ($1) RETURNING (foo_row).id > $$ > LANGUAGE sql; > > CREATE VIEW v_foo AS SELECT foo.*, foo_track_func(foo.*) FROM foo; > > SELECT * FROM v_foo; > SELECT * FROM foo_track; > > COMMIT; > > Wow! It seems just right. Thanks, let me try it out. Regards, Atri -- Regards, Atri l'apprenant