Acessing columns of parent tables with PL/pgSQL

On Thu, 27 Jun 2002 18:38:10 +0200, Hans-Juergen Schoenig
<hs@cybertec.at> wrote:

CREATE FUNCTION myinsert() RETURNS opaque AS '
BEGIN
RAISE NOTICE ''1 - NEW: %\n'', NEW.one;
RAISE NOTICE ''2 - NEW: %\n'', NEW.two;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

An error is displayed:
ERROR: function myinsert already exists with same argument types

Hans, you are still running an old version of your function
myinsert(). Better use
CREATE OR REPLACE FUNCTION myinsert() ...

Servus
Manfred

On Thursday 27 June 2002 15:38, Hans-Juergen Schoenig wrote:

I wonder if there is a way to access columns in a parent table when
running a PL/pgSQL trigger.

Here is an example:

CREATE TABLE a (
one text
);

CREATE TABLE b (
two text
) INHERITS (a);

CREATE FUNCTION myinsert() RETURNS opaque AS '
BEGIN
RAISE NOTICE ''1 - NEW: %\n'', NEW.one;
RAISE NOTICE ''2 - NEW: %\n'', NEW.two;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER mytrig BEFORE INSERT ON b
FOR EACH ROW EXECUTE PROCEDURE myinsert();

INSERT INTO b VALUES ('a1', 'b2');

An error is displayed:

CREATE
CREATE
ERROR: function myinsert already exists with same argument types

Create function fails, you have function with same name and arguments !

The basic of idea of my question is:
How can I access a column of a parent table inside a trigger function.

if i build a trigger on a child table i won't get the columns of the
parent tables.
i know that i can use a simple query using the object id for accessing
the correct row but i don't want to do it.

Hans

On Fri, 28 Jun 2002 22:43:26 +0200, Hans-Juergen Schoenig
<hs@cybertec.at> wrote:

The basic of idea of my question is:
How can I access a column of a parent table inside a trigger function.

On Fri, 28 Jun 2002 16:26:49 +0200, Hans-Juergen Schoenig
<hs@cybertec.at> wrote:

I know that this works but this is not the problem - I am talking about
predefined variables.

Hans,
sorry, I still don't get it. What's the problem with the session
transcript I sent you last week (see below)? Apart from the table
names it is what you say is not working for you.

On Thu, 27 Jun 2002 18:38:10 +0200, you wrote:

ERROR: function myinsert already exists with same argument types

I keep telling you, that the error message is telling you, that the
function could not be created, because there is already a function
with this name, and AFAICS the trigger is still executing an old
version of your function. Otherwise you would get

NOTICE: 2 - NEW: b2

and not

NOTICE: NEW: b2

This works for me:

fred=# CREATE TABLE aa (x TEXT);
CREATE
fred=# CREATE TABLE bb (y TEXT) INHERITS (aa);
CREATE
fred=# INSERT INTO bb VALUES ('x1', 'y1');
INSERT 182779 1
fred=# SELECT * FROM bb;
x | y
----+----
x1 | y1
(1 row)

fred=# SELECT * FROM aa;
x
----
x1
(1 row)

fred=# CREATE FUNCTION myinsert() RETURNS opaque AS '
fred'# BEGIN
fred'# RAISE NOTICE ''1 - NEW: %\n'', NEW.x;
fred'# RAISE NOTICE ''2 - NEW: %\n'', NEW.y;
fred'# RETURN NEW;
fred'# END;' LANGUAGE 'plpgsql';
CREATE
fred=# CREATE TRIGGER mytrig BEFORE INSERT ON bb FOR EACH ROW EXECUTE
PROCEDURE myinsert();
CREATE
fred=# INSERT INTO bb VALUES ('a1', 'b2');
NOTICE: 1 - NEW: a1

NOTICE: 2 - NEW: b2

INSERT 182782 1
fred=# SELECT version();
version
--------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
(1 row)

Servus
Manfred

Hi,
I would know if it'is possible to change system catalog tables and how.
In fact I finded that we can use -O option of postgres programm within the
initdb.
But I don't know how can I make this.
Can someone help me.
Thanks.