Laramie/programming/postgres

From ThinkPrank

Contents 1 PostgreSQL Links 2 Logging In 3 Changing db account passwords 4 Sequences 5 Functions - SQL

PostgreSQL Links

Laramie/postgresql/CreatingFunctions

http://www.postgresql.org/docs/current/interactive/explicit-joins.html

http://www.postgresql.org/docs/8.2/interactive/datatype.html

http://www.postgresql.org/docs/current/static/sql-createfunction.html

http://www.postgresql.org/docs/current/static/xfunc.html

http://homepages.inf.ed.ac.uk/s0235246/teaching/lab1.html

JDBC Driver doco: http://jdbc.postgresql.org/development/

Logging In

May have to add a path such as this:

PATH=c:\Program Files\PostgreSQL\8.1\bin;%PATH%
psql -W template1 laramie

Changing db account passwords

ALTER USER postgres WITH PASSWORD 'pg_password';

CREATE USER root WITH SUPERUSER PASSWORD 'root_password';
ALTER USER root WITH PASSWORD 'mojo';

Sequences

to set:

select setval('users_id_seq',10);

to inspect (and increment):

select nextval('users_id_seq');

Functions - SQL

Passing in a value for a string search:

CREATE OR REPLACE FUNCTION find_local(in zipcode integer, in miles integer, in keyword text) RETURNS SETOF RECORD AS 
$$  
  SELECT * from (places p join reviews r on p.id = r.placeid) 
  WHERE r.keywords ilike '%'||$3||'%' or encode(r.body,'escape') ilike '%'||$3||'%'; 
$$ LANGUAGE SQL;

Note how I dealt with BYTEA conversion to string.