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