PostgreSQL PL/pgSQL random value from array of values
Try this one: select (array[‘Yes’, ‘No’, ‘Maybe’])[floor(random() * 3 + 1)];
plpgsql
FOR EACH STATEMENT trigger example in PostgreSQL
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
ERROR: function … does not exist and HINT: No function matches the given name and argument types
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
PostgreSQL: Drop a function without parameters
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);
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
Show full stored function or procedure code in PostgreSQL?
\df+ <function_name> in psql.
Pass a SELECT result as an argument to postgreSQL function
Pass the returned user_id set as array. Create the function to accept an integer array create function get_timeinstate ( user_id_set integer[], another_param… Then call it passing the array generated by array_agg get_timeinstate( ( select array_agg(userid) from “UserState” where ctime>’2014-07-14′::timestamp ), another_param ); Inside the function: where “UserState”.userid = any (user_id_set) BTW if you are using … Read more
Check if sequence exists in Postgres (plpgsql)
You should be able query the pg_class table to see if the relname exists. IF EXISTS (SELECT 0 FROM pg_class where relname=”<my sequence name here>” ) THEN –stuff here END IF;
Optional argument in PL/pgSQL function
Since PostgreSQL 8.4 (which you seem to be running), there are default values for function parameters. If you put your parameter last and provide a default, you can simply omit it from the call: CREATE OR REPLACE FUNCTION foofunc(_param1 integer , _param2 date , _ids int[] DEFAULT ‘{}’) RETURNS SETOF foobar — declare return type! … Read more
Return zero if no record is found
You could: SELECT COALESCE(SUM(columnA), 0) FROM my_table WHERE columnB = 1 INTO res; This happens to work, because your query has an aggregate function and consequently always returns a row, even if nothing is found in the underlying table. Plain queries without aggregate would return no row in such a case. COALESCE would never be … Read more