Re: Acessing columns of parent tables with PL/pgSQL

On Fri, 28 Jun 2002 22:43:26 +0200, Hans-Juergen Schoenig
<hs(at)cybertec(dot)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(at)cybertec(dot)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