OLD and NEW are null or not defined in a statement-level trigger. Per documentation: NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in … Read more
Your function has a couple of smallint parameters. But in the call, you are using numeric literals that are presumed to be type integer. A string literal or string constant (‘123’) is not typed immediately. It remains type “unknown” until assigned or cast explicitly. However, a numeric literal or numeric constant is typed immediately. The … Read more
SELECT table1.price, table2.price AS other_price …
To get a table OID, cast to the object identifier type regclass (while connected to the same DB!). This finds the first table (or view etc.) with the given (unqualified) name along the search_path or raises an exception if not found: SELECT ‘mytbl’::regclass::oid; Schema-qualify the table name to remove the dependency on the search path: … Read more
The primary key is a column in a table. The primary key needs a unique value, which needs to come from somewhere. The sequence is a feature by some database products which just creates unique values. It just increments a value and returns it. The special thing about it is: there is no transaction isolation, … Read more
If you want to take a full backup i.e., all databases, procedures, routines, and events without interrupting any connections: mysqldump -u [username] -p -A -R -E –triggers –single-transaction > full_backup.sql -A For all databases (you can also use –all-databases) -R For all routines (stored procedures & triggers) -E For all events –single-transaction Without locking the … Read more
This will return all the details you want to know select * from information_schema.triggers; or if you want to sort the results of a specific table then you can try SELECT event_object_table ,trigger_name ,event_manipulation ,action_statement ,action_timing FROM information_schema.triggers WHERE event_object_table=”tableName” — Your table name comes here ORDER BY event_object_table ,event_manipulation; the following will return table … Read more
In Postgres functions can be overloaded, so parameters are necessary to distinguish overloaded functions. To unambiguously identify a function you can put only types of its parameters. DROP FUNCTION my_func(INT);
Postgresql historically doesn’t support procedural code at the command level – only within functions. However, in Postgresql 9, support has been added to execute an inline code block that effectively supports something like this, although the syntax is perhaps a bit odd, and there are many restrictions compared to what you can do with SQL … Read more
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html Basically you declare error handler which will call rollback START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT;