RE: trouble with trigger/function??? - Mailing list pgsql-general

Excellent, thanks.  I really apperciate your help.  One FINAL question...

compiling...i get a compile error when i try to run my procedure.  i then
dumbed it down to use the example from the documentation...(note that i
changed the language to plpgsql.)

CREATE FUNCTION onefn() RETURNS int4 AS '
    BEGIN
    SELECT 1 AS RESULT;
END;'
LANGUAGE 'plpgsql';

this gives me a compile error:

NOTICE: plpgsql: ERROR during compile of onefn near line 1
"RROR: parse error at or near "


I tried a handful of variations...including returning opaque...that gives me
a different error (typeidTypeRelid: Invalid type - oid = 0)


thanks
chris


-----Original Message-----
From: Nelson Ferreira Jr [mailto:nelson@radix.com.br]
Sent: Tuesday, October 03, 2000 9:42 AM
To: cmarkiew@commnav.com
Cc: 'Postgres (E-mail)'
Subject: Re: [GENERAL] 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