Обсуждение: Access NEW and OLD from function called by a rule

Hello,

I was wondering if it was possible to get a hold of the NEW and OLD
variables available in a Rule and pass them to a function? Maybe there
is another (better) way of accomplishing what I try to do, so I'll
sketch you my testing layout:


CREATE TABLE clients (
   id SERIAL PRIMARY KEY,
   name text
);


CREATE TABLE persons  (
   id SERIAL PRIMARY KEY,
   first_name text,
   last_name text
);

CREATE TABLE t_workers (
   id SERIAL PRIMARY KEY,
   person_id integer REFERENCES persons(id),
   client_id integer REFERENCES clients(id)
);


CREATE TABLE t_contacts (
   id SERIAL PRIMARY KEY,
   person_id integer REFERENCES persons(id),
   client_id integer REFERENCES clients(id)
);

-- view containing all worker data
CREATE VIEW workers
AS
   SELECT w.*, p.first_name, p.last_name FROM t_workers AS w
   INNER JOIN persons AS p ON ( w.person_id = p.id );

Now for inserting data in  the workers view I created a rule:

CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
   INSERT INTO persons ( first_name, last_name )
      VALUES ( NEW.first_name, NEW.last_name );

   INSERT INTO t_workers ( person_id, client_id )
      VALUES ( currval('persons_id_seq'), NEW.client_id );
);

This works. Then I also have a t_contacts table where I want do the same
with, I create a view called contacts and a rule called insert_contact.
Later on I will be having more views containing data from persons. So I
thought I could make some kind of macro of the "INSERT INTO persons .."
part. I saw PostgreSQL has support for CREATE FUNCTION (something I'm
not really familiar with). This is what I wanted to do:

I create a FUNCTION to insert data into persons:

CREATE OR REPLACE FUNCTION insert_person() RETURNS OPAQUE AS '
   BEGIN
      INSERT INTO persons ( first_name, last_name )
         VALUES ( NEW.first_name, NEW.last_name );
      RETURN NULL;
   END
' LANGUAGE 'plpgsql';

And I will call the FUNCTION from the isnert_worker RULE

CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
  SELECT insert_person();

   INSERT INTO t_workers ( person_id, client_id )
      VALUES ( currval('persons_id_seq'), NEW.client_id );
);

If I try to insert data into workers, the following happens:

test_db=# insert into workers ( first_name, last_name ) VALUES ( 'John',
'Doe');
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "insert_person" line 2 at SQL statement

PostgreSQL obviously complains about NEW not available, how can I make
it available? Is this the way to do it?


Sincerely,

Frodo Larik

Hi to all,

Actually I try to authenticate my Linux Postgres installation against Active
Directory, I find 3 solution to use:

1) LDAP
2) Pam and Kerberos
3) Kerberos alone

The first require the modification of the active directory schema, and I
prefer to avoid such responsibility.

For the 2 kerberos solution I don't find to much documentation, I try to
compile postgres with kerberos using this configure flag:

    --with-krb5=/usr/
    --with-includes=/usr/include/
    --with-libraries=/usr/lib/

    My kerberos installation is in
    /usr/bin and /usr/sbin         for the binary
    /usr/lib/                libkrb5.* e libk5crypto.so
and libkadm5
    /usr/include/            krb5.h heaser file

But seems that I miss something because when I put the krb5 word in the
pg_hba.conf and I try to connect the system give me the error:

psql -U postgres -d template1 -h 192.168.0.205

psql: Kerberos 5 authentication failed

--from the system log--
postgres[26793]: [2-1] LOG:  Kerberos recvauth returned error 103
postgres[26793]: [3-1] FATAL:  Kerberos5 authentication failed for user
"postgres"
postgres[26795]: [2-1] LOG:  Kerberos recvauth returned error 103
postgres[26795]: [3-1] FATAL:  Kerberos5 authentication failed for user
"postgres@OWORD.LOCAL"

--from the postgres log--
postgres: Software caused connection abort from krb5_recvauth


I also create with KTPASS a principal for the windows user POSTGRES and put
it in the keytab file that the configure script search for.

The kinit utility work well for any user I try to use.


After this not enthusiastics result I try with PAM and postgres (I just have
another installation that work well with pam-ldap), and sounds good, now I'm
able to authenticate the postgres user but not my and other user.

--system log--
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): entry:
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid):
get_user_info(): Conversation error
postgres[26991]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): exit:
failure
postgres[26991]: [2-1] LOG:  pam_authenticate failed: Error in service
module
postgres[26991]: [3-1] FATAL:  PAM authentication failed for user "ronzanid"
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): entry:
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid):
getpwnam():
postgres[26992]: pam_krb5: pam_sm_authenticate(postgresql ronzanid): exit:
failure
postgres[26992]: [2-1] LOG:  pam_authenticate failed: User not known to the
underlying authentication module
postgres[26992]: [3-1] FATAL:  PAM authentication failed for user "ronzanid"

I hope someone can help, any hint, useful web pages, or documentation is
very appreciate.

Thanks in advance
Dario

Frodo Larik <lists@elasto.nl> writes:
> PostgreSQL obviously complains about NEW not available, how can I make
> it available? Is this the way to do it?

No.  You seem to have read something about trigger functions, but this
usage is not a trigger function.  You need to do it more like this:

regression=# CREATE OR REPLACE FUNCTION insert_person(workers) returns void as $$
regression$# begin
regression$#   INSERT INTO persons ( first_name, last_name )
regression$#      VALUES ( $1.first_name, $1.last_name );
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
regression(# SELECT insert_person(new.*);
regression(# INSERT INTO t_workers ( person_id, client_id )
regression(# VALUES ( currval('persons_id_seq'), NEW.client_id );
regression(# );
CREATE RULE
regression=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe');
 insert_person
---------------

(1 row)

regression=#

The extra SELECT result is a bit annoying --- you could maybe hide that
by invoking the function within the rule INSERT, say by having it return
the inserted persons id.

I think passing "new.*" to a function from a rule works since about 7.4
or so.

            regards, tom lane

Hi Tom,

Tom Lane wrote:

>Frodo Larik <lists@elasto.nl> writes:
>
>
>>PostgreSQL obviously complains about NEW not available, how can I make
>>it available? Is this the way to do it?
>>
>>
>
>No.  You seem to have read something about trigger functions, but this
>usage is not a trigger function.  You need to do it more like this:
>
>regression=# CREATE OR REPLACE FUNCTION insert_person(workers) returns void as $$
>regression$# begin
>regression$#   INSERT INTO persons ( first_name, last_name )
>regression$#      VALUES ( $1.first_name, $1.last_name );
>regression$# end$$ language plpgsql;
>CREATE FUNCTION
>regression=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
>regression(# SELECT insert_person(new.*);
>regression(# INSERT INTO t_workers ( person_id, client_id )
>regression(# VALUES ( currval('persons_id_seq'), NEW.client_id );
>regression(# );
>CREATE RULE
>regression=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe');
> insert_person
>---------------
>
>(1 row)
>
>regression=#
>
>
Thanks for tips! It works, but it seems I have to rewrite this function
for every rule??

I wanted to make te function more generic,after doing this I understand
that the argument of insert_person(workers) is a table/view name:

test_db=# CREATE OR REPLACE FUNCTION insert_person(persons) RETURNS
integer AS '
test_db'#    BEGIN
test_db'#       INSERT INTO persons ( first_name, last_name )
test_db'#          VALUES ( $1.first_name, $1.last_name );
test_db'#       RETURN currval(''persons_id_seq'');
test_db'#    END
test_db'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
test_db=#
test_db=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers
DO INSTEAD (
test_db(#   SELECT insert_person(new.*) AS person_id;
test_db(#
test_db(#    INSERT INTO t_workers ( person_id, client_id )
test_db(#       VALUES ( currval('persons_id_seq'), NEW.client_id );
test_db(# );
ERROR:  function insert_person(workers) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

That means I have to create functions like insert_person(workers) ,
insert_person(othertable) and function insert_person(anothertable).

Suggestions?

Sincerely,

Frodo Larik

Frodo Larik wrote:

> That means I have to create functions like insert_person(workers) ,
> insert_person(othertable) and function insert_person(anothertable).

I found the solution to this "problem". Create a function with a
Polymorphic Type (notice the anyelement):

CREATE OR REPLACE FUNCTION insert_person(anyelement) RETURNS integer AS $$
   BEGIN
      INSERT INTO persons ( first_name, last_name )
         VALUES ( $1.first_name, $1.last_name );
      RETURN currval('persons_id_seq');
   END
$$ LANGUAGE 'plpgsql';


more infor here:
http://www.postgresql.org/docs/8.0/interactive/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

sincerely,

Frodo Larik