Re: dynamic crosstab - Mailing list pgsql-general

2010/1/27 Pierre Chevalier <pierre.chevalier1967@free.fr>:
> Hello,
> Pavel Stehule claviota:
>>
>> ...
>> you cannot get crosstab via SELECT statement. There is workaround
>> http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
>>
>
> All right, I've just tried it: it works just fine in my case! Thanks a lot!
>
> Except a few things, but I am not (yet) familiar at all with postgresql
> functions.
> I have tried to customize a bit your function, tu suit some of my needs:
>
> - when I have NULL values in my EAV (Entity-Attribute-Value) table, I don't
> want zeroes to appear in the output table;
> - the total at the right end does not make sense in my case; I replaced it
> with a COUNT;
>
> therefore, I did as follows (changes are *inside stars*, I hope the
> formatting will work!):
>
> BEGIN
> FOR r IN EXECUTE 'SELECT DISTINCT '
>  || dimx_name || '::text AS val ' || dimx_source
> LOOP
> col_list := array_append(col_list, 'SUM(CASE ' || dimx_name
> || ' WHEN ' || quote_literal(r.val) || ' THEN ' || expr
> || ' ELSE *NULL* END) AS ' || quote_ident(r.val) || '');
> END LOOP;
> query := 'SELECT ' || dimy_name || ', '
> || array_to_string(col_list, ',')
> * || ', COUNT(' || expr || ') AS Count '*
> || dimy_source || ' GROUP BY ' || dimy_name;
> OPEN result NO SCROLL FOR EXECUTE query;
> RETURN result;
> END;
> $$ LANGUAGE plpgsql STRICT;
>
>
> Now, I still have some issues: as far as I can remember, in m$ access (yes,
> I know...), a long time ago, I used to do PIVOT queries on EAV tables, where
> I could chose which operation was to be made on the variable: simply the
> value (without GROUPing), or a SUM, AVG, etc. I don't have any running
> acce$$ handy, so I can't check this, I'm afraid.
> In the case of your function, if I understand well, the line with the GROUP
> BY does the trick. I will try to play with it. Later on.
>
>
> Something else: I am quite familiar with strict SQL, I use postgreSQL a lot,
> but I am not familiar with functions and, also, cursors. So I am a bit
> surprised by the behaviour of the cursor: I am reading doc...
> But what I would like to do is to redirect the output of the function (that
> is, the 'result' cursor) to a view, which will be used in other places. I
> thought something like FETCH INTO would do the trick, but it doesn't.
>
>
> Also, I need, at some point, to export the output to some CSV file. I
> usually do a quick bash script as follows:
>
> echo "COPY (SELECT * FROM dh_litho ORDER BY id, depto) TO stdout WITH CSV
> HEADER;" | psql bdexplo > somefile.csv
>
> And then I can feed somefile.csv to whatever program I want. I tried to do
> this with the cursor and the FETCH ALL, but it didn't work out well, as I
> had guessed...
>

hmm ...it cannot work :(. You cannot forward FETCH ALL statement on
server side - without programming in C

in this case you need small application for reading cursor and
transformation to CVS

Pavel

> pierre@duran:~$ pierre@duran:~/fix_bd_amc$ echo "COPY (
>> > SELECT do_cross_cursor('lab_pjcsa_analytecode', 'FROM
>> > lab_ana_results','sample_id',
>> >       'FROM lab_ana_results_sel ',
>> >       'value_num');
>> > FETCH ALL FROM result WITH CSV HEADER;
>> > ) TO stdout WITH CSV HEADER ;" | psql bdexplo
> bash: pierre@duran:~/fix_bd_amc$: Aucun fichier ou dossier de ce type
> pierre@duran:~$ ERREUR:  erreur de syntaxe sur ou près de « ; »
> bash: ERREUR: : commande introuvable
> bash: » : commande introuvable
> pierre@duran:~$ LIGNE 4 :       'value_num');
> bash: Erreur de syntaxe près du symbole inattendu « ) »
> pierre@duran:~$                             ^
> bash: ^ : commande introuvable
>
> (sorry about the French!)
>
>
> I could not do this trick: any idea of how I could do this? I guess I should
> wrap the whole transaction into a one-line statement to be fed to to psql,
> but I can't figure out how to do it... Some help?
>
> A+
> Pierre
>
> PS: I am used to "normal" mailing lists, but I got quite confused by the
> approach from grokbase: I thought I was posting on the grokbase list
> (http://grokbase.com/), and I see that the list pgsql-general@postgresql.org
> was the one I was posting to...
> Sorry for the noise, I am RTFMing at the moment...
>
> --
> ____________________________________________________________________________
> Pierre Chevalier
>   Mesté Duran
>   32100 Condom
>  Tél+fax  :    09 75 27 45 62
>               05 62 28 06 83
>                06 37 80 33 64
>  Émail    :     pierre.chevalier1967CHEZfree.fr
>  icq#     :     10432285
>  http://pierremariechevalier.free.fr/
>  Logiciels Libres dans le Gers: http://gnusquetaires.org/
> ____________________________________________________________________________
>
>
>
>