Emulating trigger BEFORE SELECT behavior

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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;

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general