Thread: Emulating trigger BEFORE SELECT behavior

Emulating trigger BEFORE SELECT behavior

From
Atri Sharma
Date:
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


Re: Emulating trigger BEFORE SELECT behavior

From
Alfonso Afonso
Date:
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)







Re: Emulating trigger BEFORE SELECT behavior

From
Atri Sharma
Date:
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


Re: Emulating trigger BEFORE SELECT behavior

From
Adrian Klaver
Date:
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


Re: Emulating trigger BEFORE SELECT behavior

From
Atri Sharma
Date:
> 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


Re: Emulating trigger BEFORE SELECT behavior

From
Adrian Klaver
Date:
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


Re: Emulating trigger BEFORE SELECT behavior

From
Atri Sharma
Date:
>
> 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


Re: Emulating trigger BEFORE SELECT behavior

From
Adrian Klaver
Date:
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


Re: Emulating trigger BEFORE SELECT behavior

From
Fabrízio de Royes Mello
Date:

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
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: Emulating trigger BEFORE SELECT behavior

From
Atri Sharma
Date:
> 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