Thread: Help on function creating

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 0nota_exame              | double
precision        | default 0nota_final              | double precision         | default 0conceito                |
charactervarying(5)     | default
 
''::character varyingconceito_exame          | character varying(5)     | default
''::character varyingconceito_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 

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 

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
deSouza 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 

On Fri, Dec 16, 2005 at 14:03:14 -0200, Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br> wrote:
> Guys, I can do what I need but I have other problem: How can I named
> columns with another data ? For example:

You might be better off doing this in your application rather than entirely
with sql.