From: Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql <pgsql-sql@postgresql.org>
Cc: osvaldo_kussama@yahoo.com.br
Cc: aaronkoning@gmail.com
Subject: Re: Help on function creating
Date: Fri, 16 Dec 2005 14:03:14 -0200
Message-ID: <43A2E542.7000204@yahoo.com.br> (raw)
In-Reply-To: <20051207162158.GC1315@phlogiston.dyndns.org>
References: <4396D50D.5060407@yahoo.com.br>
<20051207162158.GC1315@phlogiston.dyndns.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: clark_vr@yahoo.com.br, ajs@crankycanuck.ca, osvaldo_kussama@yahoo.com.br, aaronkoning@gmail.com
Subject: Re: Help on function creating
In-Reply-To: <43A2E542.7000204@yahoo.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox