PostgreSQL examples

http://darthdeus.github.io/blog/2013/08/19/postgresql-basics-by-example/

To look at a stored procedure:

Use this to find the procedure name:

SELECT routine_schema As schema_name, routine_name As procedure_name FROM information_schema.routines;

Then use this from the psql prompt to read the sql:

SELECT pg_get_functiondef(( SELECT oid FROM pg_proc WHERE proname = '<function_name>'));

To create a table with an auto-incrementing index:

database=# CREATE TABLE VLS_TEST(
id INTEGER DEFAULT nextval('jobs_tags_id_seq'::regclass) NOT NULL,
job_id integer NOT NULL,
TEST TEXT NOT NULL);

To insert a row into this table, just don’t include the id field:

database=# INSERT INTO VLS_TEST (job_id, TEST) VALUES ( 234, 'This is test text.');
INSERT 0 1

database=# SELECT * from VLS_TEST;
id | job_id | test
------+--------+--------------------
8223 | 234 | This is test text.
(1 row)