O link General Tidbits, é a mais nova opção adicionada à página Arquivos. Este é um link para informações que permanecem sozinhas fora de um artigo e são disponibilizadas neste link com outras informações, assim que se tornam disponíveis, para serem permanentemente ligadas ao site General Bits.

O código para os exemplos que se apresentam em diversos itens e a tabela timezone discutida no último item são os primeiros passos desta nova página. Eu tenho certeza que isto irá ajudá-los a aumentar seus conhecimentos. Se você tem alguma informação que gostaria de torná-la disponível, deixe-me dar uma olhada.

Editor: elein em varlena.com

O PostgreSQL 7.3 agora suporta um sistema muito mais flexível para escrever set returning functions (SRFs) que, combinadas com algumas das novas opções de funções de permissão, permitem maior flexibilidade na configuração de schemas.

Desde que a capacidade para criar funções que retornam sets (conjunto de dados), rowtypes (linhas/registros) e conjuntos de rowtypes (result set) foi adicionada ao PostgreSQL 7.3, tenho visto muitos, muitos pedidos de exemplos. Stephan Szabo escreveu um ótimo documento no TechDocs que explica como isso funciona. Com a permissão de Stephan, eu tenho ampliado e rearranjado seu documento original em diversos itens mostrados abaixo.

Estes itens estão divididos nas seguintes seções:

• Returning Sets (Retornando conjunto de Dados)
• Returning Rowtypes (Retornando Linhas/Registros)
• Returning Sets of Rowtypes (Retornando um result set)

Os exemplos contidos se utilizam de um simples conjunto de dados mostrado a seguir. Todo o código para este dataset (tabelas), as funções e as queries (consultas) de exemplo podem ser baixadas clicando aqui.

create table department(
   id int primary key,
   name text);

create table employee(
   id int primary key,
   name text,
   salary int,
   departmentid int references department);

insert into department values (1, 'Management');
insert into department values (2, 'IT');

insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);

	select e.id as "Emp Id", e.name as "Emp Name", e.salary as "Salary",
		d.id as "Dept Id", d.name as "Dept Name" 
	from employee e , department d 
	where e.departmentid = d.id;

	 Emp Id |    Emp Name     | Salary | Dept Id | Dept Name  
	--------+-----------------+--------+---------+------------
	      1 | John Smith      |  30000 |       1 | Management
	      2 | Jane Doe        |  50000 |       1 | Management
	      3 | Fairlie Reese   |  63000 |       1 | Management
	      4 | Jack Jackson    |  60000 |       2 | IT
	      5 | Harold Bibsom   |  40000 |       2 | IT
	      6 | Julio Garcia    |  70000 |       2 | IT
	      7 | Bernice Johnson |  55000 |       2 | IT
	      8 | Lily Leong      |  67000 |       2 | IT
	      9 | Abby Wood       |  57000 |       2 | IT
	     10 | Jeff Jeffries   |  52000 |       2 | IT
	     11 | Geordie O'Hare  |  42000 |       2 | IT
	(11 rows)

Compartilhe e Divirta-se,
Elein

Editor: elein em varlena.com

Funções que retornam sets (conjuntos de dados) permitem à você retornar uma série de valores. Eles são invocados de dentro da cláusula FROM dependendo o que eles retornarão. Eles não são chamados diretamente de uma lista de colunas de destino (target list) ou da cláusula WHERE. Eles podem ser chamados somente onde quer que o nome de uma tabela possa ser posicionado.

	SELECT * FROM mySetFunction(); 

Não é muito utilizado no caso de retornar um tipo ordinário (simples, primitivo), no entanto, este é um bom exemplo para ilustrar como funciona uma função que retorna um conjunto de dados (sets). Quando retornamos um tipo genérico todas as funcionalidades do plpgsql e outras funções e operadores estarão disponíveis para construção do valor de retorno. Contudo, este exemplo é muito simples e não poderia ser de outra maneira, não se preocupem com isso.

Esta função mostra as informações dos funcionários ligados a um departamento com salários maiores do que a quantia especificada. As pastes chaves para retornar o conjunto de dados (set) estão em negrito.

   CREATE OR REPLACE FUNCTION getemployeedid ( integer )
      RETURNS SETOF integer as '
      DECLARE 
         myrow RECORD;
         retval integer;
      BEGIN
         FOR myrow IN SELECT * FROM employee where salary >= $1 LOOP
            RETURN NEXT myrow.departmentid;
         END LOOP;
         RETURN;
      END;
   ' language 'plpgsql';

	--
	-- Número do departamento de todos os funcionários
	--
	elein=# select * from getemployeedid(0);
	 getemployeedid 
	----------------
	              1
	              1
	              2
	(3 rows)
	--
	-- Cada linha representa uma pessoa no departamento que recebe acima de 50000.
	-- Equivalente a 
	-- select d.id, d.name from employees e, department d
	-- where e.departmentid = d.id;
	--
	elein=# select id, name from getemployeedid(50000) e, department d where e = id;
	 id |    name    
	----+------------
	  1 | Management
	  2 | IT
	(2 rows)

	--
	-- Quantos funcionários recebem acima de 50000 em cada departamento
	--
	elein=# select count(*), g from getemployeedid(50000) g group by g;
	 count | g 
	-------+---
	     2 | 1
	     6 | 2
	(2 rows)

Colaboradores: elein em varlena.com, Stephan Szabo sszabo em megazone23.bigpanda.com

A capacidade de retornar rowtypes (linhas/registros) foi adicionada no PostgreSQL juntamente com a capacidade de retornar conjuntos de dados (sets). Neste exemplo nós mostraremos como retornar uma tupla (registro) contendo o somatório dos salários por departamento. Serão retornados o menor, o maior e a média total do somatório dos salários por departamento. (Esta é uma tripla de dados comum de conjuntos de médias o qual esclarece a precisão de uma média. Algumas vezes a mediana é incluída também. Isto mostra a você o quanto você está margeando o salário de CEO na média...)

Antes de criar uma função retornando um rowtype (linha/registro), primeiro você deve criar uma variável composta do tipo rowtype descrevendo a tupla de saída da função. (Nota: O Informix suporta rowtypes não nomeados, ou seja, sem definição da variável. O PostgreSQL até o momento não.) A sintaxe para criar um rowtype é similar à criação de uma tabela.

CREATE TYPE deptavgs AS ( minsal int, maxsal int, avgsalary int8);

As partes desta função que são específicas para retornar rowtypes são declaradas como variáveis que serão retornadas, r, e construirão a tupla de retorno. Um rowtype (linha/registro) é declarado com o nome do seu tipo seguido por um percentual, seguido ainda, pela palavra reservada "rowtype". A tupla de retorno é referenciada e acessada com a notação usual de "ponto" da mesma maneira que com uma tabela deve ser.

   CREATE or REPLACE FUNCTION avgdept() RETURNS deptavgs AS
   '
   DECLARE
      r deptavgs%rowtype;
      dept record;
      bucket int8;
      counter int;
   BEGIN
      bucket   := 0;
      counter  := 0;
      r.maxsal := 0;
      r.minsal := 0;
      FOR dept IN SELECT sum(salary) AS salary, d.id AS department
                  FROM employee e, department d WHERE e.departmentid = d.id
                  GROUP BY department LOOP
         counter := counter + 1;
         bucket  := bucket + dept.salary;
         IF r.maxsal <= dept.salary OR r.maxsal = 0 THEN
            r.maxsal := dept.salary;
         END IF;
         IF r.minsal >= dept.salary OR r.minsal = 0 THEN
            r.minsal := dept.salary;
         END IF;
      END LOOP;

      r.avgsalary := bucket/counter;

      RETURN r;
   END
   ' language 'plpgsql';

	elein=# select * from avgdept();
	 minsal | maxsal | avgsalary 
	--------+--------+-----------
	  60000 |  80000 |     70000
	(1 row)

Colaboradores: elein em varlena.com, Stephan Szabo sszabo em megazone23.bigpanda.com

Agora nós chegamos à parte mais interessante, gerando um conjunto de registros (result set) à partir de uma função, usando uma função que retorna um SETOF (conjunto de) de rowtype. Assim como visto no passado, esta metodologia foi usada para produzir a primeira geração de gateways (portas de acesso) no Illustra. Usando a função como camada de acesso para outras bases de dados com recursos para trabalhar com diversos parâmetros, nós criamos registros para o servidor Illustra que foram originadas de outras bases de dados.

A seguinte função se comporta como conjuntos de departamentos. Os valores de retorno serão o id do departamento, o salário mínimo do departamento, o salário máximo do departamento e a média salarial do departamento. O rowtype salavgs será criado contendo as colunas apropriadas para o retorno destes valores. A função irá selecionar os salários de todos os departamentos, ordenando-os por departamento.

A parte interessante é como os valores retornados são manipulados. O chamada RETURN NEXT retorna o valor apropriado e quando a função é chamada novamente, ela agiliza na instrução seguinte o RETURN NEXT. Estas funções também requerem uma instrução final RETURN por causa disso.

A seleção percorre através de todos registros de funcionários do conjunto. E a partir disto o conjunto de médias que queremos retornar como valores, só os teremos quando nós tivermos percorrido todos os registro em cada departamento. Nós saberemos quando será isso quando chegarmos a um id diferente de departamento ou se houver uma interrupção no LOOP. Quando o departamento muda e nós retornamos o valor, os conjuntos de dados necessitarão ser reinicializados para que somente os dados do próximo departamento fiquem contidos nesta informação. Adicionalmente a isso, nós devemos saber que quando quisermos pegar o primeiro registro, nós podemos reinicializar os conjuntos de dados para chegar a ele.

A criação do tipo e a criação da função são descritas abaixo, com as partes interessantes em negrito:

	CREATE TYPE salavgs AS
	   (deptid integer, minsal integer, maxsal integer, avgsalary int8);
	CREATE OR REPLACE FUNCTION avgsal() RETURNS SETOF salavgs AS 
	'
	DECLARE
	   s salavgs%ROWTYPE;
	   salrec RECORD;
	   bucket int8;
	   counter int;
	BEGIN
	   bucket   :=0;
	   counter  :=0;
	   s.maxsal :=0;
	   s.minsal :=0;
	   s.deptid :=0;
	   FOR salrec IN SELECT salary AS salary, d.id AS department
	               FROM employee e, department d WHERE e.departmentid = d.id
	               ORDER BY d.id LOOP
	      IF s.deptid = 0 THEN
	         s.deptid := salrec.department;
	         s.minsal := salrec.salary;
	         s.maxsal := salrec.salary;
	         counter  := counter + 1;
	         bucket   := bucket + salrec.salary;
	      ELSE
	         IF s.deptid = salrec.department THEN
	            IF s.maxsal <= salrec.salary THEN
	               s.maxsal := salrec.salary;
	            END IF;
	            IF s.minsal >= salrec.salary THEN
	               s.minsal := salrec.salary;
	            END IF;
	            counter := counter + 1;
	            bucket  := bucket + salrec.salary;
	         ELSE
	            s.avgsalary := bucket/counter;
	            RETURN NEXT s;
	            s.deptid := salrec.department;
	            s.minsal := salrec.salary;
	            s.maxsal := salrec.salary;
	            counter  := 1;
	            bucket   := salrec.salary;
	         END IF;
	      END IF;
	   END LOOP;
	   s.avgsalary := bucket/counter;
	   RETURN NEXT s;
	   RETURN;
	END '
	LANGUAGE 'plpgsql';

	--
	-- Média salarial por departamento
	--
	select * from avgsal();
 	 deptid | minsal | maxsal | avgsalary 
	--------+--------+--------+-----------
       1 |  30000 |  63000 |     47666
       2 |  40000 |  70000 |     55375
 (2 rows)

	elein=# select d.name, a.minsal, a.maxsal, a.avgsalary
	elein-# from avgsal() a, department d
	elein-# where d.id = a.deptid;
    	name    | minsal | maxsal | avgsalary 
	------------+--------+--------+-----------
 	Management |  30000 |  63000 |     47666
 	IT         |  40000 |  70000 |     55375
	(2 rows)

Esta técnica é uma ótima maneira de gerar dinamicamente registros que por alguma razão não são possíveis (ou viáveis) de serem gerados por uma subselect. Conjuntos extravagantes de informações como apresentados acima são feitos em um loop. Você está limitado somente pelo que você mesmo pode fazer em plpgsql e este é um limite muito fácil de transpor.

Colaboradores: elein em varlena.com

Você está cansado de buscar através de src/backend/utils/adt/datetime.c a lista de timezones válidos? Aqui está uma Tabela de TimeZones baseada no datetime.c a partir de dados extraídos pelo Aditya. Ele mostra todos os timezones válidos no PostgreSQL.

Colaboradores: Aditya aditya em grot.org, elein em varlena.com