pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp on function creating
5+ messages / 3 participants
[nested] [flat]
* Help on function creating
@ 2005-12-07 12:26 Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-07 19:36 ` Re: Help on function creating Aaron Koning <aaronkoning@gmail.com>
2005-12-08 11:24 ` Re: Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-16 16:03 ` Re: Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
0 siblings, 3 replies; 5+ messages in thread
From: Alexandre Gonçalves Jacarandá @ 2005-12-07 12:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help on function creating
2005-12-07 12:26 Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
@ 2005-12-07 19:36 ` Aaron Koning <aaronkoning@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Aaron Koning @ 2005-12-07 19:36 UTC (permalink / raw)
To: Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>; +Cc: pgsql-sql
Have you considered using a view to create the report?
Aaron
On 12/7/05, Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help on function creating
2005-12-07 12:26 Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
@ 2005-12-08 11:24 ` Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2 siblings, 0 replies; 5+ messages in thread
From: Alexandre Gonçalves Jacarandá @ 2005-12-08 11:24 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql; osvaldo_kussama@yahoo.com.br; aaronkoning@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help on function creating
2005-12-07 12:26 Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
@ 2005-12-16 16:03 ` Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-17 21:35 ` Re: Help on function creating Bruno Wolff III <bruno@wolff.to>
2 siblings, 1 reply; 5+ messages in thread
From: Alexandre Gonçalves Jacarandá @ 2005-12-16 16:03 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql; osvaldo_kussama@yahoo.com.br; aaronkoning@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help on function creating
2005-12-07 12:26 Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-16 16:03 ` Re: Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
@ 2005-12-17 21:35 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2005-12-17 21:35 UTC (permalink / raw)
To: Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql; osvaldo_kussama@yahoo.com.br; aaronkoning@gmail.com
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-17 21:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-07 12:26 Help on function creating Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-07 19:36 ` Aaron Koning <aaronkoning@gmail.com>
2005-12-08 11:24 ` Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-16 16:03 ` Alexandre Gonçalves Jacarandá <clark_vr@yahoo.com.br>
2005-12-17 21:35 ` Bruno Wolff III <bruno@wolff.to>
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