Thread: Acessing columns of parent tables with PL/pgSQL

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
NOTICE:  NEW: b2

INSERT 455182 1


As you can see the column of the parent table cannot be accessed. Is
there a way to get around the problem?

I guess this would be a damn good feature which could be important for
many developers.


    Hans

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.