Show procedure definition in MySQL
SHOW CREATE PROCEDURE <name> Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. Swap PROCEDURE for FUNCTION for a stored function.
database
LANGUAGE SQL vs LANGUAGE plpgsql in PostgreSQL functions
SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Export MySQL database with triggers and procedures?
mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior: –routines – FALSE by default –triggers – TRUE by default so in mysqldump command , add –routines like : mysqldump <other mysqldump options> –routines > outputfile.sql See the MySQL documentation about mysqldump arguments.
Unwrap an array into rows in PostgreSQL
Use unnest. For example: CREATE OR REPLACE FUNCTION test( p_test text[] ) RETURNS void AS $BODY$ BEGIN SELECT id FROM unnest( p_test ) AS id; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 1;
Show all triggers in a MySQL database
The command for listing all triggers is: show triggers; or you can access the INFORMATION_SCHEMA table directly by: select trigger_schema, trigger_name, action_statement from information_schema.triggers You can do this from version 5.0.10 onwards. More information about the TRIGGERS table is here.
How to stop a Postgres script when it encounters an error?
I think the solution to add following to .psqlrc is far from perfection \set ON_ERROR_STOP on there exists much more simple and convenient way – use psql with parameter: psql -v ON_ERROR_STOP=1 better to use also -X parameter turning off .psqlrc file usage. Works perfectly for me p.s. the solution found in great post from … Read more
Show full stored function or procedure code in PostgreSQL?
\df+ <function_name> in psql.
Show the code of a function, procedure and trigger in PostgreSQL
\df+ in psql gives you the sourcecode.
Permission denied for relation in PostgreSQL
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more
How to show a MySQL warning that just happened?
SHOW WARNINGS is the only method I’m aware of, but you have to run it immediately after a query that had warnings attached to it. If you ran any other queries in between, or dropped the connection, then SHOW WARNINGS won’t work. The MySQL manual page for SHOW WARNINGS doesn’t indicate any other methods, so … Read more