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(at)hub(dot)org [mailto:pgsql-general-owner(at)hub(dot)org]On
> Behalf Of Nelson Ferreira Jr
> Sent: Tuesday, October 03, 2000 8:11 AM
> To: cmarkiew(at)commnav(dot)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