Re: trouble with trigger/function???

   Below is what you have to do to load PL/pgSQL into a database:
   Pay atention to the plpgsql.so path, if your PostgreSQL is instaled in any
other plate than /usr/local/pgsql


CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
      '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
      HANDLER plpgsql_call_handler
      LANCOMPILER 'PL/pgSQL';



                                                                          Nelson




chris markiewicz wrote:

> Gotcha.  So what's the story on pl/pgsql?  The documentation says:
>
> "PL/pgSQL is a loadable procedural language for the Postgres database
> system."
>
> What does 'loadable' mean here?  Is it just a matter of executing CREATE
> LANGUAGE?  (I assume that there is more to it than that.)  When I try to
> create a procedure now, it tells me that plpgsql is not recognized.
>
> I really appreciate your help.
> chris
>
> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Nelson Ferreira Jr
> Sent: Tuesday, October 03, 2000 8:11 AM
> To: cmarkiew@commnav.com
> Cc: Postgres (E-mail)
> Subject: Re: [GENERAL] trouble with trigger/function???
>
>       Hi,
>
>       Triggers cannot be writen in SQL. I suggest you use PL/PGSQL instead.
> In
> fact you can use any server-side language, except SQL.
>       The return type of the function must be OPAQUE, it means that the NEW
> variable is returned  (NEW represents the row that is being inserted and so
> fired the trigger)
>
> Nelson
>
> chris markiewicz wrote:
>
> > hello.
> >
> > i have been trying to create a trigger or trigger/function combination but
> i
> > have so far been unsuccessful.  my task is simple, when someone inserts a
> > row in the PERSON table, create a row in the RESOURCE table (note that i
> > have to pass parameters).  details below...
> >
> > first, the basic procedure and trigger are shown here:
> >
> > CREATE FUNCTION sp_person_resource() RETURNS bool
> >     AS 'insert into resource(rid, name, type, desc) values (1905,
> ''chris'',
> > ''person'', ''blah''); select true;'
> >     LANGUAGE 'sql';
> >
> > CREATE TRIGGER trig_person_resource BEFORE INSERT ON person
> >     FOR EACH ROW EXECUTE PROCEDURE sp_person_resource();
> >
> > i do not know what to use for a return type (i don't really need to return
> > anything).  but when i try to write a trigger to call that fn, it tells me
> > that the fn must return opaque...i modify my proc but it tells me that
> "sql
> > fns cannot return type opaque."
> >
> > what's going on?  and a side question, what is opaque?
> >
> > thanks
> > chris