PostgreSQL Prático/Imprimir

Editor: considere colocar o mês e o ano da marcação.

 sudo apt-get install build-essential libreadline5-dev zlib1g-dev gettext

./configure
make
make install
groupadd postgres
useradd -g postgres -d /usr/local/pgsql postgres
mkdir /usr/local/pgsql/data
chown postgres:postgres /usr/local/pgsql/data
passwd postgres
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

sudo gedit /etc/profile (e adicione a linha):
PATH=/usr/local/pgsql/bin:$PATH

source /etc/profile

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH

/usr/local/pgsql/bin/initdb --locale=pt_BR -D /usr/local/pgsql/data

CREATE TABLESPACE nome_tablespace [ OWNER usuário ] LOCATION 'diretório'

CREATE TABLESPACE ncluster OWNER usuário LOCATION '/usr/local/pgsql/nc';
CREATE TABLESPACE ncluster [OWNER postgres] LOCATION 'c:\\ncluster';

CREATE DATABASE bdcluster TABLESPACE = ncluster;

SET default_tablespace = tablespace1;

\db
SELECT spcname FROM pg_tablespace;

make distclean

   [ [ WITH ] [ OWNER [=] dono_bd ]

          [ TEMPLATE [=] modelo ]

          [ ENCODING [=] codificação ]

          [ TABLESPACE [=] tablespace ] ]

          [ CONNECTION LIMIT [=] limite_con ] ]

   { nome_coluna tipo_dado [ DEFAULT expressão_padrão ] [ restrição_coluna [ ... ] ]

   | restrição_tabela

   | LIKE tabela_pai [ { INCLUDING | EXCLUDING } DEFAULTS ] }

   [, ... ]

 NULL |

 UNIQUE [ USING INDEX TABLESPACE tablespace ] |

 PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |

 CHECK (expressão) |

 REFERENCES tabela_ref [ ( coluna_ref ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

   [ ON DELETE ação ] [ ON UPDATE ação ] }

 PRIMARY KEY ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |

 CHECK ( expressão ) |

 FOREIGN KEY ( nome_coluna [, ... ] ) REFERENCES tabela_ref [ ( coluna_ref [, ... ] ) ]

   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE ação ] }

  produto_no integer,

  descricao text,

  preco numeric DEFAULT 9.99

  produto_no integer,

  descricao text,

  preco numeric CHECK (preco > 0)

  produto_no integer,

  descricao text,

  preco numeric CONSTRAINT preco_positivo CHECK (preco > 0)

  produto_no integer,

  descricao text,

  desconto numeric CHECK (desconto > 0 AND desconto < 0.10),

  preco numeric CONSTRAINT preco_positivo CHECK (preco > 0),

  check (preco > desconto)

  cod_prod   integer NOT NULL CHECK (cod_prod > 0),

  nome       text    NOT NULL,

  preco      numeric NOT NULL

  cod_prod   integer UNIQUE,

  nome       text,

  preco      numeric

  cod_prod   integer,

  nome       text,

  preco      numeric,

  UNIQUE (cod_prod)

  a integer,

  b integer,

  c integer,

  UNIQUE (a, c)

  cod_prod   integer CONSTRAINT unq_cod_prod UNIQUE,

  nome       text,

  preco      numeric

  cod_prod   integer UNIQUE NOT NULL,

  nome       text,

  preco      numeric

  cod_prod   integer PRIMARY KEY,

  nome       text,

  preco      numeric

  a integer,

  b integer,

  c integer,

  PRIMARY KEY (a, c)

  cod_prod   integer PRIMARY KEY,

  nome       text,

  preco      numeric

  cod_pedido  integer PRIMARY KEY,

  cod_prod    integer,

  quantidade  integer,

  CONSTRAINT pedidos_fk FOREIGN KEY (cod_prod) REFERENCES produtos (cod_prod)

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES outra_tabela	- - a coluna de destino será a PK

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES outra_tabela (c1, c2)

  nome        text,

  populacao   float,

  altitude    int     -- (em pés)

  estado      char(2)

       AS SELECT count(*) AS num_shipped, max(ship_date), title

          FROM shipments

          JOIN editions USING (isbn)

          NATURAL JOIN books AS b (book_id)

          GROUP BY b.title

          ORDER BY num_shipped DESC;

       ORDER BY max DESC

       LIMIT 3;

  SELECT cidade, temp_min, temp_max, prcp, data, localizacao

      FROM clima, cidades

      WHERE cidade = nome;

   TO tabela [ WHERE condição ]

   DO [ ALSO | INSTEAD ] { NOTHING | comando | ( comando ; comando ... ) }

a |    b

t | sic est

f | non est

   nome            text,

   apgamento  integer[],

   agendamento   text[][]

   quadrado   integer[3][3]

   VALUES ('Bill',

   '{10000, 10000, 10000, 10000}',

   '{{"meeting", "lunch"}, {"meeting"}}');

   VALUES ('Bill',

   '{10000, 10000, 10000, 10000}',

   '{{"meeting", "lunch"}, {"training", "presentation"}}');

   VALUES ('Carol',

   '{20000, 25000, 25000, 25000}',

   '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

name  |      pay_by_quarter       |                 schedule

Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}

Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}

   VALUES ('Bill',

   ARRAY[10000, 10000, 10000, 10000],

   ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

   VALUES ('Carol',

   ARRAY[20000, 25000, 25000, 25000],

   ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

        \h para ajuda com comandos SQL

        \? para ajuda com comandos do psql

        \g ou terminar com ponto-e-vírgula para executar a consulta

        \q para sair

                                        version

PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

   * | expressão [ AS nome_saída ] [, ...]

   [ FROM item_de [, ...] ]

   [ WHERE condição ]

   [ GROUP BY expressão [, ...] ]

   [ HAVING condição [, ...] ]

   [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]

   [ ORDER BY expressão [ ASC | DESC | USING operador ] [, ...] ]

   [ LIMIT { contador | ALL } ]

   [ OFFSET início ]

   [ FOR { UPDATE | SHARE } [ OF nome_tabela [, ...] ] [ NOWAIT ] ]

   [ ONLY ] nome_tabela [ * ] [ [ AS ] alias [ ( alias_coluna [, ...] ) ] ]

   ( select ) [ AS ] alias [ ( alias_coluna [, ...] ) ]

   nome_função ( [ argumento [, ...] ] ) [ AS ] alias [ ( alias_coluna [, ...] | definição_coluna [, ...] ) ]

   nome_função ( [ argumento [, ...] ] ) AS ( definição_coluna [, ...] )

   item_de [ NATURAL ] tipo_junção item_de [ ON condição_junção | USING ( coluna_junção [, ...] ) ]

  FROM pessoal, lotacoes WHERE pessoal.siape = lotacoes.siape

  ORDER BY lotacoes.lotacao;

   { DEFAULT VALUES | VALUES ( { expressão | DEFAULT } [, ...] ) | consulta }

   [ FROM lista_de ]

   [ WHERE condição ]

   [ USING lista_util ]

   [ WHERE condição ]

SELECT p.siape, p.senha, l.lotacao FROM pessoal p CROSS JOIN lotacoes l;

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p INNER JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.siape;

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p, lotacoes l
WHERE p.siape = l.siape ORDER BY p.siape;

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p LEFT JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.siape;

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p RIGHT JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.nome;

SELECT lista_de_campos
   FROM expressão 
   [LIMIT { número | ALL }] [OFFSET inicio]

SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET 1; 

SELECT * FROM news_m LIMIT $inicio, $n_resultados

SELECT *
FROM SAMP.EMPREGADOS INNER JOIN SAMP.EQUIPES
ON EMPREGADOS.SAL�?RIO < EQUIPES.SAL�?RIO;

-- Junção das tabelas ATIV_EMP e EMPREGADOS
-- selecionar todas as colunas da tabela ATIV_EMP e
-- adicionar o sobrenome do empregado (ÚLTIMO_NOME) da tabela
-- EMPREGADOS a todas as linhas do resultado

SELECT SAMP.ATIV_EMP.*, ÚLTIMO_NOME
     FROM SAMP.ATIV_EMP JOIN SAMP.EMPREGADO
     ON ATIV_EMP.NUM_EMP = EMPREGADOS.NUM_EMP;

-- Juntar as tabelas EMPREGADOS e DEPARTAMENTOS,
-- selecionar o número do empregado (NUM_EMP),
-- o sobrenome do empregado (ÚLTIMO_NOME),
-- o número do departamento (DEP_TRAB na tabela EMPREGADOS e
-- NUM_DEP na tabela DEPARTAMENTOS)
-- e o nome do departamento (NOME_DEP)
-- de todos os empregados nascidos (DATA_NASC) antes de 1930.

SELECT NUM_EMP, ÚLTIMO_NOME, DEP_TRAB, NOME_DEP
     FROM SAMP.EMPREGADOS JOIN SAMP.DEPARTAMENTOS
     ON DEP_TRAB = NUM_DEP
     AND YEAR(DATA_NASC) < 1930;

-- Outro exemplo de "gerar" novos valores de dado,
-- utilizando uma consulta que seleciona da cláusula VALUES
-- (que é uma forma alternativa de FULLSELECT).

SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS TABELA1_VALORES(C1, C2)
JOIN (VALUES (3, 2), (1, 2),(0, 3))
AS TABELA2_VALORES(C1, C2)
ON TABELA1_VALORES.C1 = TABELA2_VALORES.C1;

C1         |C2         |C1         |2
-----------------------------------------------
3          |4          |3          |2
1          |5          |1          |2

-- Listar todos os departamentos, juntamente com o
-- número do empregado e o último nome do gerente

SELECT NUM_DEP, NOME_DEP, NUM_EMP, ÚLTIMO_NOME
FROM DEPARTAMENTOS
     INNER JOIN EMPREGADOS
     ON NUM_GER = NUM_EMP;

-- Listar todos os números do empregado e último nome, juntamente
-- com o número do empregado e último nome de seus gerentes
SELECT E.NUM_EMP, E.ÚLTIMO_NOME, M.NUM_EMP, M.ÚLTIMO_NOME
        FROM EMPREGADOS E INNER JOIN
        DEPARTAMENTOS INNER JOIN EMPREGADOS M
        ON NUM_GER = M.NUM_EMP
        ON E.DEP_TRAB = NUM_DEP;

ExpressãoTabela LEFT [ OUTER ] JOIN ExpressãoTabela
{
    ON ExpressãoBooleana
    }

--correspondência entre cidades e países
SELECT CIDADES.PA�?S, REGIÃO
FROM PA�?SES
     LEFT OUTER JOIN CIDADES
     ON ID_CIDADE=ID_CIDADE
WHERE REGIÃO = '�?sia';

-- uso da sintaxe sinônimo, LEFT JOIN, para obter exatamente
-- os mesmos resultados da exemplo acima

SELECT CIDADES.PA�?S, REGIÃO
FROM PA�?SES
     LEFT JOIN CIDADES
     ON ID_CIDADE=ID_CIDADE
WHERE REGIÃO = '�?sia';

-- Junção das tabelas EMPREGADOS e DEPARTAMENTOS,
-- selecionar o número do empregado (NUM_EMP),
-- o sobrenome do empregado (ÚLTIMO_NOME),
-- o número do departamento (DEP_TRAB na tabela EMPREGADOS e
-- NUM_DEP na tabela DEPARTAMENTOS)
-- e o nome do departamento (NOME_DEP)
-- de todos os empregados nascidos (DATA_NASC) antes de 1930

SELECT NUM_EMP, ÚLTIMO_NOME, DEP_TRAB, NOME_DEP
FROM SAMP.EMPREGADOS
     LEFT OUTER JOIN SAMP.DEPARTAMENTOS
     ON DEP_TRAB = NUM_DEP
     AND YEAR(DATA_NASC) < 1930;

-- Listar todos os departamentos, juntamente com o
-- número do empregado e o último nome do gerente,
-- incluindo os departamentos sem gerente

SELECT NUM_DEP, NOME_DEP, NUM_EMP, ÚLTIMO_NOME
FROM DEPARTAMENTOS
     LEFT OUTER JOIN EMPREGADOS
     ON NUM_GER = NUM_EMP;

ExpressãoTabela RIGHT [ OUTER ] JOIN ExpressãoTabela
{
    ON ExpressãoBooleana
    }

-- obter todos os países e cidades correspondentes,
-- incluindo os países sem nenhuma cidade

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT OUTER JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S;

-- obter todos países da �?frica e as cidades correspondentes,
-- incluindo os países sem cidades

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT OUTER JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S;
WHERE PA�?SES.REGIÃO = '�?frica';

-- uso da sintaxe sinônimo, RIGHT JOIN, para obter exatamente
-- os mesmos resultados do exemplo acima

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S
WHERE PA�?SES.REGIÃO = '�?frica';

-- a ExpressãoTabela pode ser uma OperaçãoJunção. Portanto,
-- pode haver várias operações de junção na cláusula FROM
-- Listar todos os números e último nome dos empregados,
-- juntamente com os números e último nome de seus gerentes

SELECT E.NUM_EMP, E.ÚLTIMO_NOME, M.NUM_EMP, M.ÚLTIMO_NOME
FROM EMPREGADOS E RIGHT OUTER JOIN
     DEPARTAMENTOS RIGHT OUTER JOIN EMPREGADOS M
     ON NUM_GER = M.NUM_EMP
     ON E.DEP_TRAB = NUM_DEP;

 select nome, (select max(preco) from produtos where codigo=1) as "maior preço" from produtos;

 SELECT * FROM tabela1 WHERE tabela1.col1 = 
   (SELECT col2 FROM tabela2 WHERE col2 = valor);

 SELECT name FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

 SELECT 'test' AS test, id FROM (SELECT * FROM books) AS example_sub_query;

 SELECT firstname, state, 
   CASE
     WHEN state = 'PA' THEN 'close'
     WHEN state = 'NJ' OR state = 'MD' THEN 'far'
   ELSE 'very far'
 END AS distance
 FROM friend;

SELECT campo1 FROM tabela1 WHERE EXISTS
  (SELECT 1 FROM tabela2 WHERE campo2 = tabela1.campo2);

CREATE TEMPORARY TABLE frutas (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO frutas VALUES (DEFAULT, 'banana');
INSERT INTO frutas VALUES (DEFAULT, 'maçã');

CREATE TEMPORARY TABLE alimentos (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO alimentos VALUES (DEFAULT, 'maçã');
INSERT INTO alimentos VALUES (DEFAULT, 'espinafre');

SELECT nome, CASE WHEN EXISTS (SELECT nome FROM frutas WHERE nome=a.nome)
               THEN 'sim'
               ELSE 'não'
           END AS fruta
FROM alimentos a;

SELECT nome, CASE WHEN nome IN (SELECT nome FROM frutas)
                THEN 'sim'
                ELSE 'não'
           END AS fruta
FROM alimentos;

SELECT nome, CASE WHEN nome = ANY (SELECT nome FROM frutas)
                THEN 'sim'
                ELSE 'não'
           END AS fruta
FROM alimentos;

create table amigos(
       codigo serial primary key,
       nome char(45),
       idade int
);

insert into amigos (nome, idade) values ('João Brito', 25);
insert into amigos (nome, idade) values ('Roberto', 35);
insert into amigos (nome, idade) values ('Antônio', 15);
insert into amigos (nome, idade) values ('Francisco Queiroz', 23);
insert into amigos (nome, idade) values ('Bernardo dos Santos', 21);
insert into amigos (nome, idade) values ('Francisca Pinto', 22);
insert into amigos (nome, idade) values ('Natanael', 55);

select nome,
       idade,
       case
           when idade >= 21 then 'Adulto'
           else 'Menor'
       end as status
from amigos order by nome;

create table amigos(
       codigo serial primary key,
       nome char(45),
       estado char(2)
);

insert into amigos (nome, estado) values ('João Brito', 'CE');
insert into amigos (nome, estado) values ('Roberto', 'MA');
insert into amigos (nome, estado) values ('Antônio', 'CE');
insert into amigos (nome, estado) values ('Francisco Queiroz', 'PB');
insert into amigos (nome, estado) values ('Bernardo dos Santos', 'MA');
insert into amigos (nome, estado) values ('Francisca Pinto', 'SP');
insert into amigos (nome, estado) values ('Natanael', 'SP');

select nome,
               estado,
               case
                       when estado = 'PB' then 'Fechado'
                       when estado = 'CE' or estado = 'SP' then 'Funcionando'
                       when estado = 'MA' then 'Funcionando a todo vapor'
                       else 'Menor'
               end as status
from amigos order by nome;

SELECT nota,
      (SELECT MIN(nota) FROM notas) AS menor,
      (SELECT MAX(nota) FROM notas) AS maior,
      (ROUND(SELECT AVG(nota) FROM notas)) AS media
FROM notas;

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
    [ RETURNS rettype ]
  { LANGUAGE langname
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

CREATE OR REPLACE FUNCTION olamundo() RETURNS int4
AS 'SELECT 1' LANGUAGE 'sql';
SELECT olamundo() ;
CREATE OR REPLACE FUNCTION add_numeros(nr1 int4, nr2 int4) RETURNS int4 
AS 'SELECT $1 + $2' LANGUAGE 'sql';
SELECT add_numeros(300, 700) AS resposta ;

CREATE TEMP TABLE empregados (
	nome text,
	salario numeric,
	idade integer,
	baia point
);

INSERT INTO empregados VALUES('João',2200,21,point('(1,1)'));
INSERT INTO empregados VALUES('José',4200,30,point('(2,1)'));

CREATE FUNCTION dobrar_salario(empregados) RETURNS numeric AS $$
	SELECT $1.salario * 2 AS salario;
$$ LANGUAGE SQL;

SELECT nome, dobrar_salario(emp.*) AS sonho
	FROM empregados
	WHERE empregados.baia ~= point '(2,1)';

SELECT nome, dobrar_salario(ROW(nome, salario*1.1, idade, baia)) AS sonho
	FROM empregados;

CREATE FUNCTION novo_empregado() RETURNS empregados AS $$
	SELECT text 'Nenhum' AS nome,
	1000.0 AS salario,
	25 AS idade,
	point '(2,2)' AS baia;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION novo_empregado() RETURNS empregados AS $$
	SELECT ROW('Nenhum', 1000.0, 25, '(2,2)')::empregados;
$$ LANGUAGE SQL;

SELECT novo_empregado();

SELECT * FROM novo_empregado();

CREATE TEMP TABLE teste (testeid int, testesubid int, testename text);
INSERT INTO teste VALUES (1, 1, 'João');
INSERT INTO teste VALUES (1, 2, 'José');
INSERT INTO teste VALUES (2, 1, 'Maria');

CREATE FUNCTION getteste(int) RETURNS teste AS $$
   SELECT * FROM teste WHERE testeid = $1;
$$ LANGUAGE SQL;
SELECT *, upper(testename) FROM getteste(1) AS t1;

CREATE TEMP TABLE nometabela (campo tipo);

CREATE FUNCTION getteste(int) RETURNS SETOF teste AS $$
	SELECT * FROM teste WHERE testeid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getteste(1) AS t1;

CREATE FUNCTION constroi_matriz(anyelement, anyelement) RETURNS anyarray AS $$
	SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT constroi_matriz(1, 2) AS intarray, constroi_matriz('a'::text, 'b') AS textarray;

CREATE FUNCTION eh_maior(anyelement, anyelement) RETURNS boolean AS $$
	SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT eh_maior(1, 2);

createlang plpgsql –U nomeuser nomebanco
create language plpgsql
createlang -U nomeuser -W -e plpgsql nomebanco
//Código-fonte
CREATE FUNCTION func_escopo() RETURNS integer AS $$
DECLARE
   quantidade integer := 30;
BEGIN
   RAISE NOTICE 'Aqui a quantidade é %', quantidade;  -- A quantidade aqui é 30
   quantidade := 50;
   --
   -- Criar um sub-bloco
   --
   DECLARE
       quantidade integer := 80;
   BEGIN
       RAISE NOTICE 'Aqui a quantidade é %', quantidade;  -- A quantidade aqui é 80
   END;
   RAISE NOTICE 'Aqui a quantidade é %', quantidade;  -- A quantidade aqui é 50
   RETURN quantidade;
END;

$$ LANGUAGE plpgsql;

=> SELECT func_escopo();

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
   v_string ALIAS FOR $1;
   index    ALIAS FOR $2;
BEGIN
   -- algum processamento neste ponto
END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION concatenar_campos_selecionados(in_t nome_da_tabela) RETURNS text AS $$
BEGIN
   RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION somar_tres_valores(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
   resultado ALIAS FOR $0;
BEGIN
   resultado := v1 + v2 + v3;
   RETURN resultado;
END;

$$ LANGUAGE plpgsql;

SELECT somar_tres_valores(10,20,30);

CREATE FUNCTION mesclar_campos(t_linha nome_da_tabela) RETURNS text AS $$
DECLARE
   t2_linha nome_tabela2%ROWTYPE;
BEGIN
   SELECT * INTO t2_linha FROM nome_tabela2 WHERE ... ;
   RETURN t_linha.f1 || t2_linha.f3 || t_linha.f5 || t2_linha.f7;
END;

$$ LANGUAGE plpgsql;

SELECT mesclar_campos(t.*) FROM nome_da_tabela t WHERE ... ;

CREATE or REPLACE FUNCTION data_ctl(opcao char, fdata date, fhora time) RETURNS char(10) AS $$
DECLARE
    opcao ALIAS FOR $1;
    vdata ALIAS FOR $2;
    vhora ALIAS FOR $3;
    retorno char(10);
BEGIN
    IF opcao = 'I' THEN 
	insert into datas (data, hora) values (vdata, vhora);
        retorno := 'INSERT';
    END IF;
    IF opcao = 'U' THEN 
	update datas set data = vdata, hora = vhora where data='1995-11-01';
        retorno := 'UPDATE';
    END IF;
    IF opcao = 'D' THEN 
	delete from datas where data = vdata;
        retorno := 'DELETE';
    ELSE
        retorno := 'NENHUMA';
    END IF;	
    RETURN retorno;
END;
$$
LANGUAGE plpgsql;

--select data_ctl('I','1996-11-01', '08:15');
select data_ctl('U','1997-11-01','06:36');
select data_ctl('U','1997-11-01','06:36');

CREATE OR REPLACE FUNCTION codigo_empregado (codigo INTEGER)
   RETURNS SETOF INTEGER AS $$
   DECLARE
	registro RECORD;
	retval INTEGER;
   BEGIN
	FOR registro IN SELECT * FROM empregados WHERE salario >= $1 LOOP
		RETURN NEXT registro.departamento_cod;
	END LOOP;
	RETURN;
   END;
$$ language 'plpgsql';

select * from codigo_empregado (0);
select count (*), g from codigo_empregado (5000) g group by g;

CREATE TABLE empregados(
  nome_emp text,
  salario int4,
  codigo int4 NOT NULL,
  departamento_cod int4,
  CONSTRAINT empregados_pkey PRIMARY KEY (codigo),
  CONSTRAINT empregados_departamento_cod_fkey FOREIGN KEY (departamento_cod)
      REFERENCES departamentos (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 

CREATE TABLE departamentos (codigo INT primary key,  nome varchar);
CREATE TYPE dept_media AS (minsal INT, maxsal INT, medsal INT);

create or replace function media_dept() returns dept_media as
$$
declare
	r dept_media%rowtype;
	dept record;
	bucket int8;
	counter int;
   begin
	bucket := 0;
	counter := 0;
	r.maxsal :=0;
	r.minsal :=0;
	for dept in select sum(salario) as salario, d.codigo as departamento
		from empregados e, departamentos d where e.departamento_cod = d.codigo
		group by departamento loop
	   counter := counter + 1;
	   bucket := bucket + dept.salario;

	   if r.maxsal <= dept.salario or r.maxsal = 0 then
		r.maxsal := dept.salario;
	   end if;

	   if r.minsal <= dept.salario or r.minsal = 0 then
		r.minsal := dept.salario;
	   end if;

	end loop;
	r.medsal := bucket/counter;
	return r;
   end
   $$ language 'plpgsql';

CREATE TYPE media_sal AS
	(deptcod int, minsal int, maxsal int, medsal int);
CREATE OR REPLACE FUNCTION medsal() RETURNS SETOF media_sal AS
$$
DECLARE
	s media_sal%ROWTYPE;
	salrec RECORD;
	bucket int;
	counter int;
BEGIN
	bucket :=0;
	counter :=0;
	s.maxsal :=0;
	s.minsal :=0;
	s.deptcod :=0;
	FOR salrec IN SELECT salario AS salario, d.codigo AS departamento
		FROM empregados e, departamentos d WHERE e.departamento_cod = d.codigo ORDER BY d.codigo LOOP
		IF s.deptcod = 0 THEN
			s.deptcod := salrec.departamento;
			s.minsal := salrec.salario;
			s.maxsal := salrec.salario;
			counter := counter + 1;
			bucket := bucket + salrec.salario;
		ELSE
			IF s.deptcod = salrec.departamento THEN
				IF s.maxsal <= salrec.salario THEN
					s.maxsal := salrec.salario;
				END IF;
				IF s.minsal >= salrec.salario THEN
					s.minsal := salrec.salario;
				END IF;
                                bucket := bucket + salrec.salario;
				counter := counter +1;
			ELSE
				s.medsal := bucket/counter;
				RETURN NEXT s;
				s.deptcod := salrec.departamento;
				s.minsal := salrec.salario;
				s.maxsal := salrec.salario;
				counter := 1;
				bucket := salrec.salario;
			END IF;
		END IF;
	END LOOP;
   	s.medsal := bucket/counter;
	RETURN NEXT s;
	RETURN;
END 
$$
LANGUAGE 'plpgsql';

select * from medsal()

select d.nome, a.minsal, a.maxsal, a.medsal 
from medsal() a, departamentos d 
where d.codigo = a.deptcod

CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] }
   ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]
   EXECUTE PROCEDURE nome_da_função ( argumentos )

 CREATE TABLE empregados(
  codigo int4 NOT NULL,
  nome varchar,
  salario int4,
  departamento_cod int4,
  ultima_data timestamp,
  ultimo_usuario varchar(50),
  CONSTRAINT empregados_pkey PRIMARY KEY (codigo) ) 

  CREATE FUNCTION empregados_gatilho() RETURNS trigger AS $empregados_gatilho$
    BEGIN
        -- Verificar se foi fornecido o nome e o salário do empregado
        IF NEW.nome IS NULL THEN
            RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
        END IF;
        IF NEW.salario IS NULL THEN
            RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome;
        END IF;
        --  
        -- Quem paga para trabalhar?
        --      
        IF NEW.salario < 0 THEN
            RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome;
        END IF;
        --  
        --
        -- Registrar quem alterou a folha de pagamento e quando
        --
        NEW.ultima_data := 'now';
        NEW.ultimo_usuario := current_user;
        RETURN NEW;
    END;
  $empregados_gatilho$ LANGUAGE plpgsql;

  CREATE TRIGGER empregados_gatilho BEFORE INSERT OR UPDATE ON empregados
    FOR EACH ROW EXECUTE PROCEDURE empregados_gatilho();

INSERT INTO empregados (codigo,nome, salario) VALUES (5,'João',1000);
INSERT INTO empregados (codigo,nome, salario) VALUES (6,'José',1500);
INSERT INTO empregados (codigo,nome, salario) VALUES (7,'Maria',2500);
SELECT * FROM empregados;
INSERT INTO empregados (codigo,nome, salario) VALUES (5,NULL,1000);
NEW – Para INSERT e UPDATE
OLD – Para DELETE

  CREATE TABLE empregados (
    nome  varchar NOT NULL,
    salario     integer
  );

  CREATE TABLE empregados_audit(
    operacao    char(1)   NOT NULL,
    usuario     varchar      NOT NULL,
    data        timestamp NOT NULL,
    nome    varchar      NOT NULL,
    salario     integer
  );

  CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Cria uma linha na tabela emp_audit para refletir a operação
        -- realizada na tabela emp. Utiliza a variável especial TG_OP
        -- para descobrir a operação sendo realizada.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'E', user, now(), OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'A', user, now(), NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', user, now(), NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
    END;
    $emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
  AFTER INSERT OR UPDATE OR DELETE ON empregados
    FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO empregados (nome, salario) VALUES ('João',1000);
INSERT INTO empregados (nome, salario) VALUES ('José',1500);
INSERT INTO empregados (nome, salario) VALUES ('Maria',250);
UPDATE empregados SET salario = 2500 WHERE nome = 'Maria';
DELETE FROM empregados WHERE nome = 'João';
SELECT * FROM empregados;
SELECT * FROM empregados_audit;

  CREATE TABLE empregados (
    codigo          serial  PRIMARY KEY,
    nome    varchar    NOT NULL,
    salario     integer
  );

  CREATE TABLE empregados_audit(
    usuario         varchar      NOT NULL,
    data            timestamp NOT NULL,
    id              integer   NOT NULL,
    coluna          text      NOT NULL,
    valor_antigo    text      NOT NULL,
    valor_novo      text      NOT NULL
  );

  CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Não permitir atualizar a chave primária
        --
        IF (NEW.codigo <> OLD.codigo) THEN
            RAISE EXCEPTION 'Não é permitido atualizar o campo codigo';
        END IF;
        --
        -- Inserir linhas na tabela emp_audit para refletir as alterações
        -- realizada na tabela emp.
        --
        IF (NEW.nome <> OLD.nome) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.id, 'nome', OLD.nome, NEW.nome;
        END IF;
        IF (NEW.salario <> OLD.salario) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.codigo, 'salario', OLD.salario, NEW.salario;
        END IF;
        RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
    END;
  $emp_audit$ language plpgsql;

  CREATE TRIGGER emp_audit
  AFTER UPDATE ON empregados
  FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO empregados (nome, salario) VALUES ('João',1000);
INSERT INTO empregados (nome, salario) VALUES ('José',1500);
INSERT INTO empregados (nome, salario) VALUES ('Maria',2500);
UPDATE empregados SET salario = 2500 WHERE id = 2;
UPDATE empregados SET nome = 'Maria Cecília' WHERE id = 3;
UPDATE empregados SET codigo=100 WHERE codigo=1;
ERRO:  Não é permitido atualizar o campo codigo
SELECT * FROM empregados;
SELECT * FROM empregados_audit;

create or replace function empresa_cliente_id(varchar,varchar) returns _int4 as $$
declare
	nempresa alias for $1;
	ncliente alias for $2;
	empresaid integer;
	clienteid integer;
begin
	insert into empresas(nome) values(nempresa);
	insert into clientes(fkempresa,nome)   values (currval (''empresas_id_seq''), ncliente);
	empresaid := currval(''empresas_id_seq'');
	clienteid := currval(''clientes_id_seq'');
	return ''{''|| empresaid ||'',''|| clienteid ||''}'';
end;
$$
language 'plpgsql';

create or replace function id_nome_cliente(integer) returns text as $$ 
declare
     r record;
begin
     select into r * from clientes where id = $1;
     if not found then
         raise exception ''Cliente não existente !'';
     end if;
     return r.nome;
end;
$$
language 'plpgsql';

create or replace function clientes_nomes() returns text as $$
declare
     x text;
     r record;
begin
     x:=''Inicio'';
     for r in select * from clientes order by id loop
         x:= x||'' : ''||r.nome;
     end loop;
       return x||'' : fim'';
end;  
$$
language 'plpgsql';

     SUPERUSER | NOSUPERUSER

   | CREATEDB | NOCREATEDB

   | CREATEROLE | NOCREATEROLE

   | CREATEUSER | NOCREATEUSER

   | INHERIT | NOINHERIT

   | LOGIN | NOLOGIN

   | CONNECTION LIMIT limite_con

   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'senha'

   | VALID UNTIL 'tempo_absoluto'

   | IN ROLE nome_role [, ...]

   | IN GROUP nome_role [, ...]

   | ROLE nome_role [, ...]

   | ADMIN nome_role [, ...]

   | USER nome_role [, ...]

   | SYSID uid

BEGIN;
UPDATE contas SET saldo = saldo – 100.00 WHERE codigo = 5;
SAVEPOINT meu_ponto_de_salvamento;
UPDATE contas SET saldo = saldo + 100.00 WHERE codigo = 5;
-- ops ... o certo é na conta 6
ROLLBACK TO meu_ponto_de_salvamento;
UPDATE contas SET saldo = saldo + 100.00 WHERE conta = 6;
COMMIT;

CREATE TABLE contas(codigo INT2 PRIMARY KEY, nome VARCHAR(40), saldo NUMERIC());
INSERT INTO contas values (5, 'Ribamar', 500.45);

BEGIN; -- Iniciar uma transação
UPDATE contas SET saldo = 800.35 WHERE codigo= 5;
SELECT nome,saldo FROM contas WHERE codigo = 5;
COMMIT; -- Executar todos os comandos da transação

'''Agora para testar se de fato todas as operações foram salvas execute:'''
SELECT nome,saldo FROM contas WHERE codigo = 5;

BEGIN; -- Iniciar uma transação
UPDATE contas SET saldo = 50.85 WHERE codigo= 5;
SELECT nome,saldo FROM contas WHERE codigo = 5;
COMMIT; -- Executar todos os comandos da transação

SELECT nome,saldo FROM contas WHERE codigo = 5;

BEGIN;
LOCK TABLE nometabela;
INTO TABLE nomenovo FROM nometabela;
DROP TABLE nometabela;
ALTER TABLE nomenovo RENAME TO nometabela;
COMMIT;

BEGIN;
ALTER TABLE tabela ADD COLUMN novocampo novotipodados;
UPDATE tabela SET novocampo = CAST (antigocampo novotipodados);
ALTER TABLE tabela DROP COLUMN antigocampo;
COMMIT;

BEGIN; -- Iniciar uma transação
UPDATE contas SET saldo = 50.85 WHERE codigo= 5;
SELECT nome,saldo FRON contas WHERE codigo = 5;
ROLLBACK; -- Cancelando todos os comandos da transação
SELECT nome,saldo FRON contas WHERE codigo = 5;

BEGIN; -- Iniciar uma transação
CREATE TABLE teste (id integer, nome text);
INSERT INTO teste VALUES (1, 'Teste1');
INSERT INTO teste VALUES (2, 'Teste2');
DELETE FROM teste;
COMMIT; -- gravar resultados
SELECT * FROM TESTE;

-- 

BEGIN;
CREATE TABLE teste (id integer, nome text);
INSERT INTO teste VALUES (3, 'Teste3');
INSERT INTO teste VALUES (4, 'Teste4');
DELETE FROM teste;
ROLLBACK;
SELECT * FROM TESTE;

banco=# \h set transaction

SET TRANSACTION modo_transação [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION modo_transação [, ...]

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Aqui as consultas da transação;
...
COMMIT;

 relname   | relfilenode | relpages

cep_pk     |       25140 |     2441

cep_tabela |       16949 |    27540

   FROM pg_class c, pg_class c2, pg_index i

   WHERE c.relname = 'customer'

       AND c.oid = i.indrelid

       AND c2.oid = i.indexrelid

   ORDER BY c2.relname;

   Oid     Database Name  Tablespace

 33375   bdcluster    	ncluster

 16948   cep_brasil  	pg_default

 25146   cep_full  		pg_default

 33360   controle_estoque  pg_default

 16879   municipios  	pg_default

 33340   pgbench  	pg_default

 10793   postgres  	pg_default

 10792   template0  	pg_default

 33377   template1  	pg_default

 16898   testes  	pg_default

local all   all 				md5

 host all 	 all  10.43.0.0 		255.0.0.0	   	   trust

 host all 	 all  200.217.23.234 	255.255.255.255   reject

 host all 	 all  0.0.0.0 		0.0.0.0	   	   	   trust

# FILE LOCATIONS
#hba_file = 'ConfigDir/pg_hba.conf'   # host-based authentication file

# CONNECTIONS AND AUTHENTICATION
# - Connection Settings –
#
# O parâmetro listen_address define que interfaces de rede do servidor, através de seus endereços IP, aceitam tentativas de conexão via TCP/IP
#listen_addresses = 'localhost'         # Quais endereços IP ou nomes onde serão feitas as escutas; 
                          # lista de endereços/nomes separados por vírgula;
                          # se fôr omitido, o default é 'localhost'
                          # '*' = todas interfaces de rede estarão habilitadas para escutar requisições
#port = 5432
max_connections = 100 (duas são reservadas para o superusuário)
# note: increasing max_connections costs ~400 bytes of shared memory per 
#superuser_reserved_connections = 2

# - Security & Authentication -
#authentication_timeout = 60            # 1-600, in seconds
#ssl = off
#password_encryption = on
# RESOURCE USAGE (except WAL)
# - Memory -
shared_buffers = 1000         # min 16 or max_connections*2, 8KB each
#temp_buffers = 1000          # min 100, 8KB each
#max_prepared_transactions = 5          # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1024                        # min 64, size in KB
#maintenance_work_mem = 16384           # min 1024, size in KB
#max_stack_depth = 2048                 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000      # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000   # min 100, ~70 bytes each

Algumas Configurações no postgresql.conf
...
# AUTOVACUUM PARAMETERS
#autovacuum = off			# enable autovacuum subprocess?
...
# - Locale and Formatting -

#datestyle = 'iso, mdy'  # Era o original
datestyle = 'sql, european'  # 	Formato dd/mm/yyyy
...
#client_encoding = sql_ascii		
#client_encoding = latin1		# Suporte à acentuação do Brasil
...

SELECT schemaname AS esquema, tablename AS tabela, tableowner AS dono
 FROM pg_catalog.pg_tables
 WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 ORDER BY schemaname, tablename

SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation
 FROM pg_catalog.pg_tablespace

SELECT pdb.datname AS banco, 
 pu.usename AS dono, 
 pg_encoding_to_char(encoding) AS codificacao,
 (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS comentario,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=pdb.dattablespace) AS tablespace
 FROM pg_database pdb, pg_user pu WHERE pdb.datdba = pu.usesysid ORDER BY pdb.datname

SELECT c.relname as tabela, 
 pg_catalog.pg_get_userbyid(c.relowner) AS dono, 
 pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer as registros,
 (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r' AND nspname='public'
 ORDER BY c.relname

SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
    (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
     FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
     WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
     AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname

SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation
 FROM pg_catalog.pg_tablespace            

SELECT 
 pc.oid AS prooid,
 proname,
 lanname as prolanguage,
 pg_catalog.format_type(prorettype, NULL) as proresult,
 prosrc,
 probin,
 proretset,
 proisstrict,
 provolatile,
 prosecdef,
 pg_catalog.oidvectortypes(pc.proargtypes) AS proarguments,
 proargnames AS proargnames,
 pg_catalog.obj_description(pc.oid, 'pg_proc') AS procomment
FROM pg_catalog.pg_proc pc, pg_catalog.pg_language pl
WHERE pc.oid = 'oid_da_function'::oid
AND pc.prolang = pl.oid

CREATE TEMPORARY TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);
CREATE TEMPORARY TABLE t2 (id INT REFERENCES t1, nome TEXT);
SELECT
   n.nspname AS esquema,
   cl.relname AS tabela,
   a.attname AS coluna,
   ct.conname AS chave,
   nf.nspname AS esquema_ref,
   clf.relname AS tabela_ref,
   af.attname AS coluna_ref,
   pg_get_constraintdef(ct.oid) AS criar_sql
FROM pg_catalog.pg_attribute a
   JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')
   JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
   JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
        ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
   JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')
   JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
   JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
        af.attnum = ct.confkey[1]);

SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
 FROM pg_namespace n, pg_class c, 
      pg_attribute a, pg_type t
 WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
   and a.attnum > 0        -- no system att's
   and not a.attisdropped   -- no dropped columns
   and a.attrelid = c.oid
   and a.atttypid = t.oid
 ORDER BY nspname, relname, attname;

SELECT n.nspname as esquema, c.relname as tabela  FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
 ORDER BY nspname, relname

<?php
$conexao=pg_connect("host=127.0.0.1 user=postgres password=postabir");
$sql="SELECT datname AS banco FROM pg_database ORDER BY datname";
$consulta=pg_query($conexao,$sql);
$banco = array();
$c=0;
while ($data = @pg_fetch_object($consulta,$c)) {
   $cons=$data->banco;		
   $banco[] .= $cons;
   $c++;
}
$sql2="SELECT n.nspname as esquema,c.relname as tabela  FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
ORDER BY nspname, relname";
for ($x=0; $x < count($banco);$x++){
   if ($banco[$x] !="template0" && $banco[$x] != "template1" && $banco[$x] !="postgres"){
       $conexao2=pg_connect("host=127.0.0.1 dbname=$banco[$x] user=postgres password=postabir");
       $consulta2=pg_query( $conexao2, $sql2 );		
           while ($data = pg_fetch_object($consulta2)) {
               $esquematab=$data->esquema.'.'.$data->tabela;
               $sql3="SELECT count(*) FROM $esquematab";
               $consulta3=pg_query($conexao2,$sql3);
               $res=@pg_fetch_array($consulta3);
               print 'Banco.Esquema.Tabela -> 
               '.$banco[$x].'.'.$data->esquema.'.'.$data->tabela.' - Registro(s) -  '.$res[0].'
';
               $total += $res[0];
           }
   }
}
print "Total de Registro de todas as tabelas de todos os bancos ". $total;	
?>    

select datname, oid from pg_database;

select relname, relfilenode from pg_class;

select indexrelname as indice, relname as tabela from pg_catalog.pg_statio_user_indexes as A 
INNER JOIN pg_catalog.pg_index as B  ON A.indexrelid=B.indexrelid WHERE A.schemaname='public' AND B.indisprimary = true;

psql -U user banco -E

opt_cidades_pkey | opt_cidades | id          | t          | t

            SELECT  n.nspname AS "Esquema"
            FROM  pg_catalog.pg_namespace AS n,
                   pg_catalog.pg_class AS c
             WHERE c.relnamespace = n.oid AND c.relname='opt_cidades';

     from pg_class i, pg_index idx

     where i.oid = idx.indexrelid

     and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size",

     from pg_class i, pg_index idx

     where i.oid = idx.indexrelid

     and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size"

 c.relname as "opt_cidades",

 CASE c.relkind WHEN 'r' THEN 'tabela' WHEN 'v' THEN 'view' WHEN 'i' THEN 'índice' WHEN 'S' THEN 

    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')

     AND pg_catalog.pg_table_is_visible(c.oid)

public |   opt_cidades    |  Tipo  |   Dono

public | opt_cidades      | tabela | postgres

public | opt_estado       | tabela | postgres

 n.nspname AS "Esquema",

 c.relname AS "Tabela"

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

     AND c.relname ~ '^opt_cidades$'

 OID  | Esquema |   Tabela

   pg_namespace.nspname AS esquema,

   pg_class.relname AS tabela,

   pg_attribute.attname  AS coluna_pk

FROM pg_class

   JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND

        pg_namespace.nspname NOT LIKE 'pg_%'

   JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND

        pg_attribute.attisdropped='f'

   JOIN pg_index ON pg_index.indrelid=pg_class.oid AND

        pg_index.indisprimary='t' AND

        (

        pg_index.indkey[0]=pg_attribute.attnum OR

        pg_index.indkey[1]=pg_attribute.attnum OR

        pg_index.indkey[2]=pg_attribute.attnum OR

        pg_index.indkey[3]=pg_attribute.attnum OR

        pg_index.indkey[4]=pg_attribute.attnum OR

        pg_index.indkey[5]=pg_attribute.attnum OR

        pg_index.indkey[6]=pg_attribute.attnum OR

        pg_index.indkey[7]=pg_attribute.attnum OR

        pg_index.indkey[8]=pg_attribute.attnum OR

        pg_index.indkey[9]=pg_attribute.attnum

        )

ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname;

CREATE TEMPORARY TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);

CREATE TEMPORARY TABLE t2 (id INT REFERENCES t1, nome TEXT);

SELECT

   n.nspname AS esquema,

   cl.relname AS tabela,

   a.attname AS coluna,

   ct.conname AS chave,

   nf.nspname AS esquema_ref,

   clf.relname AS tabela_ref,

   af.attname AS coluna_ref,

   pg_get_constraintdef(ct.oid) AS criar_sql

FROM pg_catalog.pg_attribute a

   JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')

   JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)

   JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND

        ct.confrelid != 0 AND ct.conkey[1] = a.attnum)

   JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')

   JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)

   JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND

        af.attnum = ct.confkey[1]);

 esquema  | tabela | coluna |   chave    | esquema_ref | tabela_ref | coluna_ref |             

pg_temp_1 | t2     | id     | t2_id_fkey | pg_temp_1   | t1         | id | FOREIGN KEY (id) 

        FROM pg_class c, pg_attribute a, pg_type t

        WHERE c.relname = 'apagar' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid

        ORDER BY a.attnum;

         FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia

         WHERE (bc.oid = i.indrelid)

             AND (ic.oid = i.indexrelid)

             AND (ia.attrelid = i.indexrelid)

             AND (ta.attrelid = bc.oid)

             AND (bc.relname = 'apagar')

             AND (ta.attrelid = i.indrelid)

             AND (ta.attnum = i.indkey[ia.attnum-1])

             ORDER BY index_name, tab_name, column_name

         FROM pg_relcheck, pg_class bc

         WHERE rcrelid = bc.oid

             AND bc.relname = 'apagar'

              AND NOT EXISTS (

                  SELECT *

                      FROM pg_relcheck as c, pg_inherits as i

                      WHERE i.inhrelid = pg_relcheck.rcrelid

                          AND c.rcname = pg_relcheck.rcname

                          AND c.rcsrc = pg_relcheck.rcsrc

                          AND c.rcrelid = i.inhparent

              )

SELECT pg_class.relname, pg_attribute.attname, pg_type.typname, pg_attribute.atttypmod-4 
  FROM pg_class, pg_attribute, pg_type 
  WHERE pg_attribute.attrelid = pg_class.oid 
  AND pg_attribute.atttypid = pg_type.oid 
  AND pg_class.relname = 'apagar' 
  AND pg_attribute.attname = 'descricao'

esquema |     tabela     |  campo  |         Type          |                     Default                     | NOT NULL

CREATE OR REPLACE FUNCTION Dados_Tabela(varchar(30))
 RETURNS SETOF tabela_estrutura AS '
DECLARE
 r tabela_estrutura%ROWTYPE;
 rec RECORD;
 vTabela alias for $1;
 eSql TEXT;
BEGIN
 eSql := SELECT
 CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) , CAST(attrs.attname AS TEXT), 
 CAST("Type" AS TEXT), CAST("Default" AS TEXT), attrs.attnotnull
      FROM
              (SELECT c.oid, n.nspname, c.relname
              FROM pg_catalog.pg_class c
              LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
       JOIN 
              (SELECT a.attname, a.attrelid,
              pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
                     (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
                     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
              as "Default", a.attnotnull, a.attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
       ON (attrs.attrelid = rel.oid )
       WHERE relname LIKE '% || vTabela || %'
       ORDER BY attrs.attnum;
 FOR r IN EXECUTE eSql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
       RAISE EXCEPTION Tabela % não encontrada, vTabela;
END IF;
RETURN;
END
' 
LANGUAGE 'plpgsql';

SELECT * FROM Dados_Tabela('tabela');

SELECT rolname as "Donos", datname as Bancos 
 FROM pg_roles, pg_database
 WHERE pg_roles.oid = datdba
 ORDER BY rolname, datname;

SELECT n.nspname as esquema, c.relname as tabela
 FROM pg_class c, pg_namespace n
 WHERE c.relnamespace=n.oid
   and c.relkind = 'r'                   -- not indices, views, etc
   and n.nspname not like 'pg\\_%'       -- not catalogs
   and n.nspname != 'information_schema' -- not information_schema
 ORDER BY nspname, relname;

SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
 FROM pg_namespace n, pg_class c, 
      pg_attribute a, pg_type t
 WHERE n.oid = c.relnamespace
   and c.relkind = 'r'     -- no indices
   and n.nspname not like 'pg\\_%' -- no catalogs
   and n.nspname != 'information_schema' -- no information_schema
   and a.attnum > 0        -- no system att's
   and not a.attisdropped   -- no dropped columns
   and a.attrelid = c.oid
   and a.atttypid = t.oid
 ORDER BY nspname, relname, attname;

SELECT n.nspname, o.oprname AS binary_op,
      format_type(left_type.oid, null) AS left_opr,
      format_type(right_type.oid, null) AS right_opr,
      format_type(result.oid, null) AS return_type
 FROM pg_namespace n, pg_operator o, pg_type left_type, 
      pg_type right_type, pg_type result
 WHERE o.oprnamespace = n.oid
   and o.oprkind = 'b'         -- binary
   and o.oprleft = left_type.oid
   and o.oprright = right_type.oid
   and o.oprresult = result.oid
 ORDER BY nspname, left_opr, right_opr;

SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
 FROM pg_namespace n, pg_aggregate a, 
      pg_proc p, pg_type t
 WHERE p.pronamespace = n.oid
   and a.aggfnoid = p.oid
   and p.proargtypes[0] = t.oid
 ORDER BY nspname, proname, typname;

select datname, oid from pg_database;

select relname, relfilenode from pg_class;

 SELECT pg_attribute.attnum AS index, 
   attname AS field, 
   typname AS type, 
   atttypmod-4 as length, 
   NOT attnotnull AS "null", 
   adsrc AS default 
 FROM pg_attribute, 
   pg_class, 
   pg_type, 
   pg_attrdef 
 WHERE pg_class.oid=attrelid 
   AND pg_type.oid=atttypid 
   AND attnum >0 
   AND pg_class.oid=adrelid 
   AND adnum=attnum 
   AND atthasdef='t' 
   AND lower(relname)='datas' 
 UNION 
 SELECT pg_attribute.attnum AS index, 
   attname AS field, 
   typname AS type, 
   atttypmod-4 as length, 
   NOT attnotnull AS "null", 
    AS default 
 FROM pg_attribute, 
   pg_class, 
   pg_type 
 WHERE pg_class.oid=attrelid 
   AND pg_type.oid=atttypid 
   AND attnum>0 
   AND atthasdef='f' 
   AND lower(relname)='datas';

 AccessConnect = 

 Conex.ConnectionString = AccessConnect

 Conex.Open AtivConex.ActiveConnection = Conex

   public static void main(String args[]) { 

       //String url = "jdbc:postgresql://10.0.1.53:5432/agenda"; 

       String url = "jdbc:postgresql://localhost:5432/agenda"; 

       //String url = "jdbc:postgresql:agenda";    //Assim pega os defaults 

       Connection con; 

       String query = "select * from amigos”; 

       Statement stmt; 

       try { 

           Class.forName("org.postgresql.Driver"); 

       } catch(java.lang.ClassNotFoundException e) { 

           System.err.print("ClassNotFoundException: "); 

           System.err.println(e.getMessage()); 

       } 

       try { 

           con = DriverManager.getConnection(url,"postgres", "postgres"); 

           stmt = con.createStatement(); 

           ResultSet rs = stmt.executeQuery(query); 

           ResultSetMetaData rsmd = rs.getMetaData(); 

           int numberOfColumns = rsmd.getColumnCount(); 

           int rowCount = 1; 

           while (rs.next()) { 

               System.out.println("Registro " + rowCount + ":  "); 

               for (int i = 1; i <= numberOfColumns; i++) { 

                   System.out.print("   Campo " + i + ":  "); 

                   System.out.println(rs.getString(i)); 

               } 

               System.out.println(""); 

               rowCount++; 

           } 

           stmt.close(); 

           con.close(); 

       } catch(SQLException ex) { 

           System.err.print("SQLException: "); 

           System.err.println(ex.getMessage()); 

       } 

   } 

  CurrentProject.Connection.Execute StrSql2

 Dim cnn as new ADODB.Connection

 cnn.Open "DSN=my_dbs_dsn_name" 'or a full PostgreSQL connection string to save a trip to the 

 cnn.Execute StrSql2

 "codigo" BIGINT NOT NULL, 
 "cod_pedido" BIGINT, 
 "quantidade" INTEGER, 
 CONSTRAINT "pedido_itens_pkey" PRIMARY KEY("codigo"), 
 CONSTRAINT "pedido_itens_fk" FOREIGN KEY ("cod_pedido")
   REFERENCES "public"."pedidos"("codigo")
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
   NOT DEFERRABLE

   $randstring = join("", @chars [map{rand @chars} ( 1 .. 8 ) ]);

   $randnum = join("", @numbers [map{rand @numbers} ( 1 .. 8 ) ]);

   $randletter = join("", @single_chars [map{rand @single_chars} (1)]);

   print OUTPUT

   #print OUTPUT "INSERT INTO index_teste VALUES($count,'$randstring',$randnum,'$randletter');\n";

   $count."\t".$randstring."\t".$randnum."\t".$randletter."\n";

   $count++;

   global $con;

   $str="SELECT DATE '$data_final' - DATE '$data_inicial'";

   $recordset = pg_query($con, $str);

   $diferença=pg_fetch_array($recordset);

   return $diferença[0];

     ln -s /mnt/hdb/pg_xlog /usr/local/pgsql/data/pg_xlog 

   $randstring = join("", @chars [map{rand @chars} ( 1 .. 8 ) ]);

   $randnum = join("", @numbers [map{rand @numbers} ( 1 .. 8 ) ]);

   $randletter = join("", @single_chars [map{rand @single_chars} (1)]);

   print OUTPUT

   #print OUTPUT "INSERT INTO index_teste VALUES($count,'$randstring',$randnum,'$randletter');\n";

   $count."\t".$randstring."\t".$randnum."\t".$randletter."\n";

   $count++;

                                  ProdA    

                       x                       |                                                       

                                               |                                                       

                                               |Y                                              |Y                                                      

                                               |                                                       

                                               |                                                       

PostgreSQL Prático/Imprimir

1 - Introdução editar

2 - Instalação editar

Pré-requisitos para instalação do PostgreSQL num UNIX editar

Instalação no Linux editar

2 – Instalação no Windows editar

3 - DDL (Data Definition Language) editar

4 - DML (Data Manipulation Language) editar

DML editar

Consultas

Trabalhando corretamente com select

4.2 - Junções de Tabelas ou Consultas editar

INNER JOIN

Operação JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

4.3 – Sub consultas editar

5 - Funções Internas editar

5.1 – Funções de Strings editar

5.2 – Funções Matemáticas editar

NULL ou não NULL, eis a questão!

5.3 – Funções de Agrupamento (Agregação) editar

5.4 – Funções de Data/Hora editar

5.5 - Formatação de Tipos de Dados editar

5.6 - Conversão Explícita de Tipos (CAST) editar

6 - Funções Definidas pelo Usuário e Triggers editar

6 - Funções no PostgreSQL editar

6.2 - Funções em Pl/pgSQL editar

6.3 - Triggers (Gatilhos) editar

7 - DCL (Data Control Language) - Administração editar

7.1 - Usuários, grupos e privilégios editar

8 – Transações editar

9 - Administração editar

9.1 - Backup e Restore editar

9.2 - Importar e Exportar editar

9.3 - Converter editar

Otimização e Desempenho editar

How to optimize PostgreSQL database size

Uma ótima fonte de consulta

VACUUM

ANALYZE

Recomendação

Dicas de Desempenho

Separando bancos e Logs

Plano de Consulta

Reinício do ID de Transações

Alerta

10 - Replicação editar

Cluster != Replicação

11 - Configurações editar

11.1 - pg_hba.conf editar

11.2 - postgresql.conf editar

12 - Metadados (Catálogo) editar

13 - Conectividade editar

14 - Ferramentas editar

14.1 - psql editar

14.2 - phpPgAdmin editar

14.3 – PgAdmin editar

14.4 - EMS PostgreSQL Manager editar

14.5 - Azzurry Clay (modelagem) editar

14.6 – DbVisualizer editar

14.7 – Openoffice2 Base editar

15 - Apêndices editar

15.1 – Planejamento e Projeto de Bancos de Dados editar

15.2 – Implementação de Banco de Dados com o PostgreSQL - Modelo Físico editar

15.3 - Integridade Referencial - Postgresql editar

15.4 - Dicas Práticas de uso do SQL editar

15.5 – Dicas sobre Desempenho e Otimizações do PostgreSQL editar

16 – Exercícios editar

17 - Referências editar