Thread: 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