[GENERAL] How can I alter a function?
Hi, How can I change the definition of a function without breaking all tables, which use the function? Sascha
[GENERAL] ALTER FUNCTION
Hi, are there plans for an ALTER FUNCTION statement for Postgresql? I think functions are completely unuseable, when it is not possible to change the definition of a function. A bugfix in a function requires the export of all data, a redefinition of the function and a complete reimport. Or is there a simpler way? Sascha
[GENERAL] CREATE TRIGGER ON UPDATE
Hi,
Isn't it possible to change the data of a row from it's trigger
function?
I would like to have some update statistics in a database. So I
creeated a basetable with a number_of_updates attribute. Then I
created a child tables, that inherits the attributes. In order to
update the attributes I wrote a trigger function for the updates. But
it does not work. Is there something missing or is this not possible?
This shows what I have done:
--
users=> CREATE TABLE basetable
users-> (
users-> created datetimeDEFAULT 'now',
users-> last_modified datetimeDEFAULT 'now',
users-> last_syncronizeddatetimeDEFAULT '-infinity',
users-> number_of_updates int DEFAULT 0
users-> );
CREATE
users=>
users=> CREATE FUNCTION basetable_update() RETURNS opaque AS '
users'> BEGIN
users'> new.last_modified := now();
users'> new.number_of_updates := old.number_of_updates + 1;
users'> RETURN new;
users'> END;
users'> ' LANGUAGE 'plpgsql';
CREATE
users=>
users=> CREATE TABLE toptable
users-> (
users-> somedata1 text,
users-> somedata2 text
users-> )
users-> INHERITS (basetable);
CREATE
users=>
users=> CREATE TRIGGER toptable_update after UPDATE
users-> ON toptable FOR EACH ROW EXECUTE PROCEDURE basetable_update();
CREATE
users=>
users=> insert into toptable (somedata1) values ('jau');
INSERT 341407 1
users=> select somedata1, last_modified, number_of_updates from toptable;
somedata1|last_modified |number_of_updates
-++-
jau |Thu Dec 02 13:34:32 1999 MET|0
(1 row)
users=> update toptable set somedata1 = 'jau neu' where somedata1 = 'jau';
UPDATE 1
users=> select somedata1, last_modified, number_of_updates from toptable;
somedata1|last_modified |number_of_updates
-++-
jau neu |Thu Dec 02 13:34:32 1999 MET|0
(1 row)
--
The number_of_updates attribute does not change, although the
basetable_update function writes it. Can anybody show me the error?
Sascha
Re: [GENERAL] ALTER FUNCTION
Adriaan Joubert <[EMAIL PROTECTED]> writes: | Just drop the function, drop all triggers that use the function, | re-create the function and recreate all triggers. If the function is | called by other PL functions, you need to drop and re-install those as | well. If you keep them all in a big file, every one preceded by drop, | you can just reload the file (with \i into psql) whenever you have | changed something. No need to dump any data. When I use the function in a CHECK constrain of a table, I have to destroy the table. Or is it possible to refresh the reference to the function by an ALTER TABLE statement? By the way: when I drop a table that is used by another table via INHERITS, I get the warning, that the table is used and that I can not drop it. Why are functions handled different? I is really a problem, when there are broken tables in a database and nobody knows it. Sascha
Re: [GENERAL] ALTER FUNCTION
Holger Klawitter <[EMAIL PROTECTED]> writes: | > are there plans for an ALTER FUNCTION statement for Postgresql? I | > think functions are completely unuseable, when it is not possible to | > change the definition of a function. A bugfix in a function requires | > the export of all data, a redefinition of the function and a complete | > reimport. Or is there a simpler way? | | It might sound simple minded, but | | BEGIN WORK; | LOCK TABLE t1; | ... | DROP FUNCTION ... | CREATE FUNCTION ... | ... | UNLOCK TABLE t1; | COMMIT WORK; | | should work. Whether you actually have to lock the tables depends on your | application. (unplugging your host from the net might be easier :-) I think I didn't have expained the problem well enough: Take a look at this example: First I create a function that checks if the argument is 1: users=> CREATE FUNCTION check_func(int) RETURNS boolean AS ' users'> BEGIN users'> IF $1 = 1 THEN users'> RETURN TRUE; users'> ELSE users'> RETURN FALSE; users'> END IF; users'> END; users'> ' LANGUAGE 'plpgsql'; CREATE Then I create a table that uses the function as an CHECK constrain: users=> CREATE TABLE data_table users-> ( users-> dataint CHECK (check_func(data)) users-> ); CREATE Now I can insert data into the table: users=> INSERT INTO data_table (data) VALUES (1); INSERT 341478 1 Later I find out that my check constrain was wrong and I drop the function and create the new one: users=> DROP FUNCTION check_func(int); DROP users=> CREATE FUNCTION check_func(int) RETURNS boolean AS ' users'> BEGIN users'> IF $1 = 2 THEN users'> RETURN TRUE; users'> ELSE users'> RETURN FALSE; users'> END IF; users'> END; users'> ' LANGUAGE 'plpgsql'; CREATE Now I insert the new data and find out that the data_table is broken: users=> INSERT INTO data_table VALUES (1); ERROR: init_fcache: Cache lookup failed for procedure 341467 Locking does not help here. bis später... Sascha
[GENERAL] operator *=
Hi, I have two tables "user_t" and "email_alias_t". The "user_t" table has the two fields "last_name" and "linux_login" and the "email_alias_t" table has the two fields "login" and "alias". In some rows in the "user_t" table the entry "linux_login" is NULL. Now I need all rows from "user_t" and for those rows which have a "linux_login" I need the "alias" column from "email_alias_t". My SQL book descibes the *= operator which should be used for those queries but Postgres does not have it. I found out that it is possible to do the select with a union. This union: select last_name, direct_inward_number, linux_login, alias from user_t, email_alias_t where last_name LIKE 'Ni%' AND linux_login = login union select last_name, direct_inward_number, linux_login, linux_login from user_t where last_name LIKE 'Ni%' AND linux_login = NULL order by 1; produces the right output: last_name|direct_inward_number|linux_login|alias -++---+- Niebisch |2608|jni00514 |niebisch.jaroslaw Nienaß |1365| | Nilles |2478|pni00423 |nilles.peter Nix |2358|yni00155 |nix.yvonne My question: Is such a union the only way to emulate the *= operator in Postgres or is there a better way? bis später... Sascha
[GENERAL] Function arguments
Hi, is there a way to define a function, that accepts a TEXT value and NULL as an argument? bis später... Sascha
[GENERAL] bool and NOT
Hi, how should I write this: todolist=# select name from tasks_t where id = 5 and NOT done; ERROR: argument to NOT is type 'numeric', not 'bool' bis später... Sascha
