2010/1/27 Pierre Chevalier <pierre.chevalier1967@xxxxxxx>: > 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@xxxxxxxxxxxxxx > 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/ > ____________________________________________________________________________ > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
