RE: trouble with trigger/function???

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(at)radix(dot)com(dot)br]
Sent: Tuesday, October 03, 2000 9:42 AM
To: cmarkiew(at)commnav(dot)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(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