
On Mac OS X the Postgres binaries are saved in /usr/local/bin. Here is a handy Postgres launcher that can be saved in the user’s directory (as postgres_start.sh for example):
su -l postgres -c "/usr/local/bin/pg_ctl start -D /Users/postgres/datadir"
Where datadir is the directory (owned by the postgres user) where Postgres has the data files.
To create a custom tablespace in Postgres, make an empty directory first and use that as the location for the tablespace in pgAdmin3.
Sample Ruby code to access Postgres; notice that the first line is needed to set up the environment; without it the require fails.
#! /usr/bin/env ruby
#
# original file src/test/examples/testlibpq.c
# Modified by Razvan
# Calls PL/SQL function in Postgres
require 'postgres'
def main
norecs = 0
pghost = "localhost"
pgport = 5432
pgoptions = nil
pgtty = nil
dbname = "razvan"
begin
conn = PGconn.connect(pghost,pgport,pgoptions,pgtty,dbname)
res = conn.exec("BEGIN")
res.clear
res = conn.exec("SELECT * FROM insertrt('another row')")
if (res.status != PGresult::TUPLES_OK)
raise PGerror,"RB-Error executing command.\n"
end
printf("\nRB-Results\n")
res.result.each do |tupl|
tupl.each do |fld|
printf("RB-%-15s",fld)
norecs = norecs + 1
end
end
res = conn.exec("END")
printf("\nRB-Records: %i\n", norecs)
res.clear
conn.close
rescue PGError
if (conn.status == PGconn::CONNECTION_BAD)
printf(STDERR, "RB-Connection lost.")
else
printf(STDERR, "RB-Error:" )
printf(STDERR, conn.error)
end
exit(1)
end #rescue
end #end def main
main #invoke code
This calls the following Postgres plpgsql function:
CREATE OR REPLACE FUNCTION insertrt(data character varying)
RETURNS bigint AS
$BODY$
DECLARE
id bigint;
BEGIN
id := 0;
IF EXISTS(SELECT * FROM "RTable") THEN
SELECT MAX("Id") INTO id FROM "RTable";
END IF;
id := id + 1;
INSERT INTO "RTable" ("Data", "Id")
VALUES(data, id);
RAISE NOTICE 'New id is %', id;
RETURN id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insertrt(character varying) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION insertrt(character varying) TO postgres;
To execute this function do a SELECT * FROM insertrt( ‘parameter’ ). Interesting in the function, SELECT INTO variable. Also notice “ ‘s used to enclose field names and table names. The output of RAISE NOTICE is displayed by the Ruby console.
Since gems does not work with OS X Tiger’s Ruby, the Postgres adapter has to be built manually.
