postgre_snippets.sql

-- Grant For Schema
GRANT ALL PRIVILEGES ON SCHEMA pdg TO project1;
-- Grant For Table
GRANT ALL PRIVILEGES ON TABLE pdg.proposal TO project1;
-- Grant For Sequence
GRANT USAGE, SELECT ON SEQUENCE pdg.proposal_id_seq TO project1;
-- Grant For View
GRANT SELECT ON pdg.vw_program_hierarchy TO project1;

-- //-- Source: http://www.postgresql.org/docs/8.3/static/sql-grant.html --//

-- Create Table
CREATE TABLE pdg.proposal__file_revision
(
  id integer NOT NULL,
  proposal_id integer NOT NULL,
  proposal_metadata character varying(500),
  file_id integer,
  revision_no smallint,
  note character varying(1000),
  created_at timestamp without time zone,
  created_by integer,
  CONSTRAINT proposal__file_revision_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE pdg.proposal__file_revision OWNER TO postgres;
GRANT ALL ON TABLE pdg.proposal__file_revision TO postgres;
GRANT ALL ON TABLE pdg.proposal__file_revision TO project1;
-- End Create Table

-- Create View
CREATE VIEW pdg.vw_employee_pdg AS
SELECT * FROM table_name WHERE condition=1 ORDER BY field
-- End Create View

-- Create Index
CREATE UNIQUE INDEX proposal_id_idx ON pdg.ipp USING btree (proposal_id);

-- ALTER COLUMN FROM varchar TO integer
ALTER TABLE pdg.proposal ALTER COLUMN proposal_type TYPE smallint USING (proposal_type::integer);

-- Select Enum Type (As Array)
SELECT ENUM_RANGE(null::pdg.ipp_statuses)
-- Select Enum Type (As Rows)
SELECT UNNEST(ENUM_RANGE(null::pdg.ipp_statuses))
-- Select Column From Table
SELECT column_name FROM information_schema.columns WHERE table_name='table_name';