I’ve got a bunch of these things collecting dust in a text file in my home directory. Maybe they’ll help somebody else out.
Transpose display in psql with \x
matt=# select now(), 1 + 1 as sum;
now | sum
-------------------------------+-----
2008-08-20 13:00:44.178076-04 | 2
(1 row)
matt=# \x
Expanded display is on.
matt=# select now(), 1 + 1 as sum;
-[ RECORD 1 ]----------------------
now | 2008-08-20 13:00:46.930375-04
sum | 2
matt=# \x
Expanded display is off.
matt=# select now(), 1 + 1 as sum;
now | sum
-------------------------------+-----
2008-08-20 13:01:19.725394-04 | 2
(1 row)
See how long every query takes
matt=# \timing
Timing is on.
matt=# select now();
now
-----------------------------
2008-12-18 12:31:50.60008-05
(1 row)
Time: 76.322 ms
By the way, you can put these into your $HOME/.psqlrc to always turn on timing at the beginning of a session::
$ cat $HOME/.psqlrc
-- Report time used for each query.
\timing
Define a function in python
First you have to install plpython. Then connect to the database as somebody with sufficient privileges and then type:
CREATE LANGUAGE 'plpythonu';
to allow functions defined in plpython.
Here’s a toy example:
matt=# create or replace function snoz (INT)
returns INT AS
$$
x = args[0]
return x * x * x
$$ language 'plpythonu';
CREATE FUNCTION
And here it is in use:
matt=# select 3, snoz(3);
?column? | snoz
----------+------
3 | 27
(1 row)
Use a trigger to set the modified date column
First define a function to set the modifieddate column:
create or replace function ts_modifieddate()
returns TRIGGER as
'
BEGIN
NEW.modifieddate = now();
RETURN NEW;
END;
' language 'plpgsql';
Now set up the trigger to call that function::
create trigger set_modifieddate before update
on knid for each row
execute procedure ts_modifieddate();
Use a trigger to set one column based on other columns
I got a table like this:
create table snoz (
a bool default false,
b bool default false,
c bool default false
);
I want a trigger to set c to true when a and/or b is true.
First, I define a function that does what I want:
create or replace function set_c()
returns trigger as $$
BEGIN
if NEW.a = true or NEW.b = true then NEW.c = true; END IF;
RETURN NEW;
END;
$$ language 'plpgsql';
Now I wire up that trigger to execute when I want:
create trigger update_c
before insert or update
on snoz
for each row
execute procedure set_c();
Here it is all in action:
matt=# insert into snoz (a, b) values (false, false), (false, false);
INSERT 0 2
matt=# select * from snoz;
a | b | c
---+---+---
f | f | f
f | f | f
(2 rows)
matt=# insert into snoz (a, b) values (false, true), (true, false);
INSERT 0 2
matt=# select * from snoz;
a | b | c
---+---+---
f | f | f
f | f | f
f | t | t
t | f | t
(4 rows)
See, it works!