Обсуждение: trouble with trigger/function???
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
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
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
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
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
Nelson Ferreira Jr <nelson@radix.com.br> writes:
> 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)
Type "OPAQUE" means too many different things at the moment --- in some
uses it means "VOID" and in some other ones it means "some type that's
not described in pg_type". ON-INSERT trigger functions have to return
the tuple to be inserted, and HeapTuple is not a type known to pg_type,
so OPAQUE is the right declaration; but it's confusing and error-prone.
I've been thinking about proposing a cleanup that would replace OPAQUE
with multiple special-purpose pseudo-datatypes, but I'm not sure that
improving clarity is worth the price of making everyone change all
their trigger function definitions ...
regards, tom lane
Nelson Ferreira Jr <nelson@radix.com.br> writes:
> 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';
Or just use the "createlang" script, which wraps all that detail up for
you.
Whichever way you do it, you have to be Postgres superuser to do these
CREATEs.
regards, tom lane
"chris markiewicz" <cmarkiew@commnav.com> writes:
> NOTICE: plpgsql: ERROR during compile of onefn near line 1
> "RROR: parse error at or near "
The message looks just like that, eh? It's probably complaining about a
carriage return character in the text. Don't prepare your scripts with
DOS-style newline sequences in them. (For 7.1, plpgsql has been fixed
to accept \r as whitespace, but current versions don't...)
regards, tom lane
Ah, you are correct.
Okay, I think I'm close...when I run my function (or any function), I get
the following runtime error:
ERROR: typeidTypeRelid: Invalid type - oid = 0
The exact text of the fn is:
CREATE FUNCTION sp_person_resource() RETURNS OPAQUE AS '
BEGIN INSERT INTO resource(resourceid, name, type, description) values
(1906, ''chris'', ''person'', ''com.commnav....'');
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
What does that error mean? Note that I was getting the same error when I
was running a SELECT fn as well.
thanks
chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 03, 2000 11:38 AM
To: cmarkiew@commnav.com
Cc: 'Nelson Ferreira Jr'; 'Postgres (E-mail)'
Subject: Re: [GENERAL] trouble with trigger/function???
"chris markiewicz" <cmarkiew@commnav.com> writes:
> NOTICE: plpgsql: ERROR during compile of onefn near line 1
> "RROR: parse error at or near "
The message looks just like that, eh? It's probably complaining about a
carriage return character in the text. Don't prepare your scripts with
DOS-style newline sequences in them. (For 7.1, plpgsql has been fixed
to accept \r as whitespace, but current versions don't...)
regards, tom lane
please disregard my last question (below). looks like all of my problems,
including the typeidTypeRelid exception, were related to shady characters
from dos.
thanks again for all of your help.
chris
-----Original Message-----
From: chris markiewicz [mailto:cmarkiew@commnav.com]
Sent: Tuesday, October 03, 2000 12:59 PM
To: 'Tom Lane'
Cc: 'Nelson Ferreira Jr'; 'Postgres (E-mail)'
Subject: RE: [GENERAL] trouble with trigger/function???
Ah, you are correct.
Okay, I think I'm close...when I run my function (or any function), I get
the following runtime error:
ERROR: typeidTypeRelid: Invalid type - oid = 0
The exact text of the fn is:
CREATE FUNCTION sp_person_resource() RETURNS OPAQUE AS '
BEGIN INSERT INTO resource(resourceid, name, type, description) values
(1906, ''chris'', ''person'', ''com.commnav....'');
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
What does that error mean? Note that I was getting the same error when I
was running a SELECT fn as well.
thanks
chris
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 03, 2000 11:38 AM
To: cmarkiew@commnav.com
Cc: 'Nelson Ferreira Jr'; 'Postgres (E-mail)'
Subject: Re: [GENERAL] trouble with trigger/function???
"chris markiewicz" <cmarkiew@commnav.com> writes:
> NOTICE: plpgsql: ERROR during compile of onefn near line 1
> "RROR: parse error at or near "
The message looks just like that, eh? It's probably complaining about a
carriage return character in the text. Don't prepare your scripts with
DOS-style newline sequences in them. (For 7.1, plpgsql has been fixed
to accept \r as whitespace, but current versions don't...)
regards, tom lane