In PL/PgSQL parser, assignment operator is defined as assign_operator : ‘=’ | COLON_EQUALS ; This is a legacy feature, present in source code since 1998, when it was introduced – as we can see in the PostgreSQL Git repo. Starting from version 9.4 it is oficially documented. This idiosyncrasy – of having two operators for … Read more
To catch the error message and its code: do $$ begin create table yyy(a int); create table yyy(a int); — this will cause an error exception when others then raise notice ‘The transaction is in an uncommittable state. ‘ ‘Transaction was rolled back’; raise notice ‘% %’, SQLERRM, SQLSTATE; end; $$ language ‘plpgsql’; Haven’t found … Read more
Return selected columns CREATE OR REPLACE FUNCTION get_user_by_username(_username text , _online bool DEFAULT false) RETURNS TABLE ( user_id int , user_name varchar , last_activity timestamptz ) LANGUAGE plpgsql AS $func$ BEGIN IF _online THEN RETURN QUERY UPDATE users u SET last_activity = current_timestamp — ts with time zone WHERE u.user_name = _username RETURNING u.user_id , … Read more
It is not supported to look beyond the current row in a CHECK constraint. http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says: A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns … Read more
Basic query This query creates all necessary DDL statements: SELECT ‘DROP FUNCTION ‘ || oid::regprocedure FROM pg_proc WHERE proname=”my_function_name” — name without schema-qualification AND pg_function_is_visible(oid); — restrict to current search_path Output: DROP FUNCTION my_function_name(string text, form text, maxlen integer); DROP FUNCTION my_function_name(string text, form text); DROP FUNCTION my_function_name(string text); Execute the commands after checking plausibility. … Read more
Does it matter which columns I select? No, it doesn’t matter. Even if SELECT 1 FROM table WHERE … FOR UPDATE is used, the query locks all rows that meet where conditions. If the query retrieves rows from a join, and we don’t want to lock rows from all tables involved in the join, but … Read more
Postgres 9 DO $$ — declare BEGIN /* pl/pgsql here */ END $$;
Postgres 9.1 added FOREACH to loop over arrays: DO $do$ DECLARE _arr int[] := ‘{1,2,3,4}’; _elem int; — matching element type BEGIN FOREACH _elem IN ARRAY _arr LOOP RAISE NOTICE ‘%’, _elem; END LOOP; END $do$; db<>fiddle here Works for multi-dimensional arrays, too: Postgres flattens the array and iterates over all elements. To loop over … Read more
This has been asked many times. A possible solution can be found here: https://stackoverflow.com/a/6527838/552671 This solution requires both an UPDATE and INSERT. UPDATE table SET field=’C’, field2=’Z’ WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, ‘C’, ‘Z’ WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3); With Postgres 9.1 it is possible to … Read more
temprow is a record variable which is bound in turn to each record of the first SELECT. So you should write: FOR temprow IN SELECT * FROM user_data.users ORDER BY user_seasonpts DESC LIMIT 10 LOOP INSERT INTO user_data.leaderboards (season_num,player_id,season_pts) VALUES (old_seasonnum,temprow.userd_id,temprow.season_ptss); END LOOP; This loop could be further simplified as a single query: INSERT INTO … Read more