Thread: how to get trigger start function not procedure
Hi folks,
my name is chris and I'm pretty new to pSQL.
I used to work with MS SQL Server 6.5 and have
some problems adjusting myself with pSQL.
Now my question. As mentioned in the subject I
want a trigger to fire an sql-function - not a
c procedure - although this would be much cooler.
I'm trying to get something to work I always did
with MS SQL. When a dataset is inserted in a
table I want a trigger to be fired to set the
creation-date and -time plus the user and a trigger
setting the update-date -time and user.
Does anyone has any idea of how to do this with pSQL???
In MS SQL server i just created a trigger with the
necessary SQL-commands inside the trigger but in pSQL
it seems that trigger just start procdures.
thanks in advance
chris
--
create function set_timestamp() returns opaque as 'begin new.timestamp := now(); new.user := getpgusername() end if; return new; end;' language 'plpgsql'; create trigger MyUpdateTrigger before insert or update on YourTable for each row execute procedure set_timestamp; ----- Original Message ----- From: "chris Günther" <guenther@uscreen.de> To: <pgsql-novice@postgresql.org> Sent: Friday, December 29, 2000 11:23 AM Subject: how to get trigger start function not procedure > Hi folks, > > my name is chris and I'm pretty new to pSQL. > I used to work with MS SQL Server 6.5 and have > some problems adjusting myself with pSQL. > > Now my question. As mentioned in the subject I > want a trigger to fire an sql-function - not a > c procedure - although this would be much cooler. > I'm trying to get something to work I always did > with MS SQL. When a dataset is inserted in a > table I want a trigger to be fired to set the > creation-date and -time plus the user and a trigger > setting the update-date -time and user. > > Does anyone has any idea of how to do this with pSQL??? > > In MS SQL server i just created a trigger with the > necessary SQL-commands inside the trigger but in pSQL > it seems that trigger just start procdures. > > thanks in advance > > chris > > -- >
On Sat, 30 Dec 2000 08:28:52 -0500
"rob" <rob@cabrion.com> wrote:
> create function set_timestamp() returns opaque as
> 'begin
> new.timestamp := now();
> new.user := getpgusername()
> end if;
> return new;
> end;' language 'plpgsql';
>
> create trigger MyUpdateTrigger before insert or update on YourTable for each
> row execute procedure set_timestamp;
>
>
Hi and thanks a lot for your answer. Now it seems I have another problem:
When I try to follow your example - slightly changed to fit my needs -
I get the message:
ERROR: Unrecognized language specified in a
CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal and
the created procedural languages.
Does this meen my installation doen't support plpgsql? I have installed
pSQL on a RedHat 6.2 System and used moreless the defaults when compiling
it.
How can I find out what languages are available for my installation and
should plpgsql not be part of every installation
chris
chris Gnther wrote: >Hi and thanks a lot for your answer. Now it seems I have another problem: >When I try to follow your example - slightly changed to fit my needs - >I get the message: > > ERROR: Unrecognized language specified in a > CREATE FUNCTION: 'plpgsql'. > Recognized languages are sql, C, internal and > the created procedural languages. You need to enable plpgsql in each database where it is to be used. Run the script src/pl/plpgsql/enable_plpgsql -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Give to him that asketh thee, and from him that would borrow of thee turn not away." Matthew 5:42
On Sat, Dec 30, 2000 at 02:54:05PM +0100, chris Günther wrote: > On Sat, 30 Dec 2000 08:28:52 -0500 > "rob" <rob@cabrion.com> wrote: ... > Hi and thanks a lot for your answer. Now it seems I have another problem: > When I try to follow your example - slightly changed to fit my needs - > I get the message: > > ERROR: Unrecognized language specified in a > CREATE FUNCTION: 'plpgsql'. > Recognized languages are sql, C, internal and > the created procedural languages. You have to tell Postgres that this database is allowed to use plpgsql. There is a script createlang you can use: for me (7.0.2), it is like: createlang plpgsql dbname I think that if you give template1 as dbname, all future databases will "inherit" the language. - The same holds for the other procedural languages. BTW: Just as an aside: the 7.0.2 createlang man page / usage info is definitely not compatible to the script: dbname is not optional; and at least the name of the language is given as pgsql instead of plpgsql or so. Greetings, Albert. > > Does this meen my installation doen't support plpgsql? I have installed > pSQL on a RedHat 6.2 System and used moreless the defaults when compiling > it. > > How can I find out what languages are available for my installation and > should plpgsql not be part of every installation > > chris > > -- -------------------------------------------------------------------------- Albert Reiner <areiner@tph.tuwien.ac.at> Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------