Re: dynamic crosstab - Mailing list pgsql-general

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@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/
____________________________________________________________________________