X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 178019DC883 for ; Fri, 16 Dec 2005 20:14:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71306-10 for ; Fri, 16 Dec 2005 20:14:09 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp203.mail.sc5.yahoo.com (smtp203.mail.sc5.yahoo.com [216.136.129.93]) by postgresql.org (Postfix) with SMTP id 8EDF49DC84E for ; Fri, 16 Dec 2005 20:14:03 -0400 (AST) Received: (qmail 80696 invoked from network); 16 Dec 2005 16:04:18 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br; h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding; b=pUZqdT7NBYaECyhEB6VEnDz945gY9Sg4X02NMpoAKBuR0IW9/uQes9iKxhBmowbQs64ZjwNPLSQDz+VHQrgGnIBWUgymvHxzdgY89fcziLa7EL3MZfPkteKXavehetXKIA5umx7ud6scDhDZ8j9k9VXajVpBiEYPG9CivaZzlh8= ; Received: from unknown (HELO ?201.29.153.91?) (clark?vr@201.29.153.91 with plain) by smtp203.mail.sc5.yahoo.com with SMTP; 16 Dec 2005 16:04:10 -0000 Message-ID: <43A2E542.7000204@yahoo.com.br> Date: Fri, 16 Dec 2005 14:03:14 -0200 From: =?ISO-8859-1?Q?Alexandre_Gon=E7alves_Jacarand=E1?= User-Agent: Mozilla Thunderbird 1.0.6-7.1.20060mdk (X11/20050322) X-Accept-Language: pt-br, pt MIME-Version: 1.0 To: Andrew Sullivan CC: pgsql , osvaldo_kussama@yahoo.com.br, aaronkoning@gmail.com Subject: Re: Help on function creating References: <4396D50D.5060407@yahoo.com.br> <20051207162158.GC1315@phlogiston.dyndns.org> In-Reply-To: <20051207162158.GC1315@phlogiston.dyndns.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200512/143 X-Sequence-Number: 23685 Guys, I can do what I need but I have other problem: How can I named columns with another data ? For example: select * from crosstab('select pessoa_nome(ref_pessoa), ref_disciplina, nota_final from matricula where ref_periodo = ''25CASI'' order by 1,2', 'select ref_disciplina from disciplinas_ofer where ref_periodo = ''25CASI'' order by 1') as (Nome text, CI text, EOE text, TTI text, TS text, GQ text, GL text, GM text, GF text, CG text, GRH text, GEITI text, AMSI text, IEDSI text, MAOP text, EB text, MPC text, MES text); with this output nome | ci | eoe | tti | ts | gq | gl | gm | gf | -----------------------------------------+-----+-----+-----+-----+-----+-----+-----+----+----+-----+-------+-- Agnaldo de Souza Filho | 9 | 8 | 8 | 7 | 7 | 7 | 8.5 | 0 | Alexandre Antabi | 9.5 | 8.8 | 8 | 7 | 8.3 | 7.9 | 8.5 | 0 | Alzira Fernanda Oliveira | 9 | 9.2 | 9.1 | 9 | 7.1 | 7.5 | 8.2 In field "as ( Nome text, ...)" I'd like to get CI, EOE, TTI, ... from an table field with this names, because none of this columns are fixed, in a year I can take 5 ( five) classes and another 7 ( seven). How can I get it ? Thanks, Alexandre. Thanks Andrew, Osvaldo ( Obrigado) and Aaron !! Sory Andrew the right version number is 7.4.3 and I'll study the crosstab because it's seems to fit for sutuation. See you... Andrew Sullivan escreveu: > I think you can do this with the contrib/crosstab stuff. But AFAIK, > there's no such thing as Pg 7.5.3. > > A > > On Wed, Dec 07, 2005 at 10:26:53AM -0200, Alexandre Gonçalves Jacarandá wrote: > >>Hello everyone, I'm using postgresql form an year and I have some >>difficult to write a function to make an report. I'm running PostgreSQL >>7.5.3 on i686-pc-linux-gnu (Mandriva 10.1). My table structure is: >>Tabela "public.matricula" >> Column | Type | Modifiers >> >>-------------------------+--------------------------+------------------------------------------------- >> id | integer | not null default >>nextval('seq_matricula'::text) >> ref_contrato | integer | >> ref_pessoa | integer | >> ref_campus | integer | >> ref_curso | integer | >> ref_periodo | character varying(10) | >> ref_disciplina | integer | >> ref_curso_subst | integer | >> ref_disciplina_subst | integer | >> ref_disciplina_ofer | integer | >> nota | double precision | default 0 >> nota_exame | double precision | default 0 >> nota_final | double precision | default 0 >> conceito | character varying(5) | default >>''::character varying >> conceito_exame | character varying(5) | default >>''::character varying >> conceito_final | character varying(5) | default >>''::character varying >> >>I need to have an output like: >>ref_pessoa | ref_disciplina1 | ref_disciplina2 | ... | ref_disciplina n >>1 | nota_final1 | nota_final2 | ... | nota_final n >> >>How can I make this ? I know that I need to declare an variable an than >>make an for - loop, but I don't know how. Where can I get some examples >>for this? >>Thanks. >> >>-- >>__________________________ >>Alguns caminham pelo arco, >>eu caminho pela reta. >> >>Alexandre Gonçalves Jacarandá >>Assessor de Tecnologia de Informação >>Tel.: 0 ** 21 8131-2313 >> >> >> >> >> >> >> >>_______________________________________________________ >>Yahoo! doce lar. Faça do Yahoo! sua homepage. >>http://br.yahoo.com/homepageset.html >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq > > -- __________________________ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 _______________________________________________________ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html