SQL trigger function

[araga]

Hello there,
I am trying to create a trigger which executes an SQL function. For creating a trigger, it insists that the return type of the function being triggered has to be 'trigger' type. So I tried creating a SQL function with return type 'trigger' as:
create function display() returns trigger as 'select sum(totalhours) from log' language 'sql';
However I get an error telling- SQL functions cannot return type "trigger".
Does this mean I cannot trigger SQL statements or am I doing something wrong.
Please help me out with this.
Thanks

 

[rycamor]

AFAIK, usually the return type of a trigger function is OPAQUE. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[araga]

Thank u, but 7.3 version calls it 'trigger' but also accepts opaque from the old version as u have rightly pointed out. After searching various documentation finally I came across one which said its not possible to write trigger functions in SQL and should use rule instead.
SO I am trying to use rule now.Now I have one more problem. I am trying to write a rule to copy table to a external file:
create rule replicate as on insert to log do copy log to '\data1.txt';
But it says parser error at or near copy and does not create the rule.
Can anyone suggest what could be the problem? or is it that u cannot give a rule with copy? Ohhh.... there seems to be lot of limitations with postgres.

thanks

 

[rycamor]

What platform are you running?

'\data1.txt' is not a valid file path on a Unix system. If you are running PostgreSQL under Windows/Cygwin, I still think you would need to use Unix-style paths.

Yes, I tried, and found that SQL functions cannot return type 'trigger'. I would guess that means you shouldn't use plain SQL in a trigger. So, use PL/PgSQL. It's a much more complete language for functions anyway.

Also, I notice another logical no-op here: you are trying to define a function that displays data. Triggers cannot do that. By definition, triggers don't 'show' you anything when they run. They do their job in the background. Thus, having a trigger do a SELECT is absolutely pointless.

I personally think the PostgreSQL concept of 'rules' is way more elegant and useful than triggers for many things anyway. For example, a trigger can only be 'triggered' by an insert, update, or delete. A rule can be called even for a regular SELECT operation. (very useful for logging, for example).

And, if you want your rule to also call a function, just create a function, and include a call to that function inside the rule definition.

I haven't found that many limitations with PostgreSQL. To the contrary, actually. If you still can't get what you want, why don't you show your code, explain your need, and then we'll get to the bottom of this ;-). -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[araga]

You guessed it right. I am running postgreSQL on windows/cygwin. As for the path name is concerned Postgres assumes the folder in which I have installed cygwin as the root directory and I can successfully copy data into any file (so far I have tried .txt and .xls extensions) stored under the root, in this case data1.txt.
As far as using a select statement in the trigger function, it was just to try whether triggers accept SQL functions. I dont need anything of that sort in my applications. What I acutally need to do is the following:
I am using a table containing usernames in a text file as reference in another program of mine which is written in C. It actually reads the information from this .txt file. Now I am making a database of all the users and I want this text file to get updated everytime there is a Update or insert in the user database. Since copy statement 'copy log to '\data1.txt' worked perfectly fine, I thought if I make a rule which does a copy to data1.txt everytime a data is inserted or updated into the database then my work would be done. But now I dont know why it is not accepting copy statement as a rule.
Since I am new to this database stuff I have not explored all the possibilities yet. Your suggestion to use PL/PgSQL is a good one and I shall look upon it. BUt I would greatly appreciate if you can come up with some solution to my above mentioned problem.
I really appreciate ur previous response and look forward to more of it.
Thanks