Thread: how to get trigger start function not procedure

how to get trigger start function not procedure

From
chris Günther
Date:
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

--

Re: how to get trigger start function not procedure

From
"rob"
Date:
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
>
> --
>


Re: how to get trigger start function not procedure

From
chris Günther
Date:
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

Re: Re: how to get trigger start function not procedure

From
"Oliver Elphick"
Date:
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



Re: Re: how to get trigger start function not procedure

From
"Albert REINER"
Date:
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
--------------------------------------------------------------------------