On 01/31/2010 03:52 PM, Florent THOMAS wrote:
> Hello everybody,
>
> I'm trying to find out how to have a dynamic crosstab as in excel,
> ireport,etc...
> As i understand of the manual here :
> http://docs.postgresqlfr.org/8.4/tablefunc.html
> I can have multiple columns.
>
> Unfortunately, it se
Hello everybody,
I'm trying to find out how to have a dynamic crosstab as in excel,
ireport,etc...
As i understand of the manual here :
http://docs.postgresqlfr.org/8.4/tablefunc.html
I can have multiple columns.
Unfortunately, it seems indispensible to name the columns in the AS
clause.
Am I rig
On 1/28/2010 5:51 PM, Pierre Chevalier wrote:
while ( my @list = $get->fetchrow_array)
{
print join(',', @list), "\n";
}
It throws some insulting messages, though:
Use of uninitialized value $list[5] in join or string at
./crosstab_perl.pl line 24.
Use of uninitialized value $list[6] in join
2010/1/29 Pierre Chevalier :
> Pavel Stehule claviota:
>>>
>>> Nut... Idea! (careful...) what about if we do, just like in a VIEW, a
>>> CREATE
>>> OR REPLACE, systematically when we do this kind of function? The only
>>> drawback I can think of is that we can't have anything dependant on the
>>> V
Andy Colson claviota:
...
> be happy to post a little "get you started" code if you wanted.
here's some code, its based on Pavel's example, and dumps csv to stdout:
Hmm, pretty cryptic to my eyes...
Thanks for not writing everything on one line!
#!/usr/bin/perl
use strict;
use warnings;
use
Andy Colson claviota:
Ach! Too bad... Oh but... I used to program in C, long time ago, on
HP-UX...
How do you feel about a little perl?
Hm, I am not too familiar with perl. That's the least I can say. But,
after all, why not?
It would be pretty simple, and could generate a csv
Actually,
Pavel Stehule claviota:
Nut... Idea! (careful...) what about if we do, just like in a VIEW, a CREATE
OR REPLACE, systematically when we do this kind of function? The only
drawback I can think of is that we can't have anything dependant on the VIEW
we generate.
no, you cannot do it. You cann
On 01/28/2010 08:57 AM, Andy Colson wrote:
>>
>> How do you feel about a little perl? It would be pretty simple, and
>> could generate a csv based on any resultset (any number of columns). I'd
>> be happy to post a little get you started code if you wanted.
If you're going to go through all that,
On 1/28/2010 9:11 AM, Andy Colson wrote:
On 1/27/2010 3:49 AM, Pierre Chevalier wrote:
Pavel Stehule claviota:
...
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 I
On 1/27/2010 3:49 AM, Pierre Chevalier wrote:
Pavel Stehule claviota:
...
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.
On Wed, Jan 27, 2010 at 2:14 AM, Pavel Stehule wrote:
>
> 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
If I'm understanding what you're doing
Pavel Stehule claviota:
...
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 outp
Pavel Stehule claviota:
...
Actually, if the small application was reading cursor, and transforming it
to a VIEW, this would solve both problems at once:
something like:
CREATE VIEW crosstabbed_thing AS
(cursor_to_dataset(SELECT do_cross_cursor(...)));
no it isn't possible. VIEW have to ha
2010/1/27 Pierre Chevalier :
> Pavel Stehule claviota:
>>>
>>> ...
>>> Actually, if the small application was reading cursor, and transforming
>>> it
>>> to a VIEW, this would solve both problems at once:
>>> something like:
>>>
>>> CREATE VIEW crosstabbed_thing AS
>>> (cursor_to_dataset(SELECT do_
2010/1/27 Pierre Chevalier :
> Pavel Stehule claviota:
>>>
>>> ...
>>> 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
2010/1/27 Pierre Chevalier :
> 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 l
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)
2010/1/27 Pierre Chevalier :
> Hello,
>
> Some time ago, it was written here:
>>
>> ...
>> I think there should be a generic way in Postgres to return from an EAV
>> model. Although I have no evidence on that I keep thinking that the db must
>> be more effective at that than the application would b
Hello,
Some time ago, it was written here:
...
I think there should be a generic way in Postgres to return from an
EAV model. Although I have no evidence on that I keep thinking that
the db must be more effective at that than the application would be.
...
Yes, thanks.
The problem with those
I always hope that somebody might have something similar but
> generic - eg. create those columns automatically and just treat them all
> as text.
I came up with this amateurish one based on
http://www.ledscripts.com/tech/article/view/5.html.
Maybe someone can use it:
takes
- a select statement
> > > It occurs to me that it shouldn't be terribly difficult to make an
> > > alternate version of crosstab() that returns an array rather than
> > > tuples (back when crosstab() was first written, Postgres didn't
> > > support NULL array elements). Is this worth considering for 8.4?
> >
> > How
On Tue, Feb 19, 2008 at 11:56:08AM -0300, Alvaro Herrera wrote:
> Joe Conway wrote:
> > Erik Jones wrote:
> >> See how postgres handles filling the NULLs for you? What you'd
> >> really want to do with this would be to define some functions
> >> for setting and getting a person's answers to a gi
Alvaro Herrera wrote:
Joe Conway wrote:
It occurs to me that it shouldn't be terribly difficult to make an
alternate version of crosstab() that returns an array rather than tuples
(back when crosstab() was first written, Postgres didn't support NULL
array elements). Is this worth considerin
Joe Conway wrote:
> Erik Jones wrote:
>> See how postgres handles filling the NULLs for you? What you'd really
>> want to do with this would be to define some functions for setting and
>> getting a person's answers to a given question or set of questions so
>> that you could implement some k
On Fri, Feb 15, 2008 at 9:56 AM, Balázs Klein <[EMAIL PROTECTED]> wrote:
> > given that answers for a questionnaire are stored as a
> > batch
>
> Not in our setup - for all sorts of reasons (preserving responses on a
> connection failure or restart, monitoring response latency in real time,
> c
> -Original Message-
> > Do youthink there is a way to ensure that the order of the values in the
> > array below is the same for each person?
> >
> > tbl(eID, aID, value)
> >
> > Select eID, array_accum(value) from
> > (
> > (Select Distinct eID from tbl) e
> > CROSS JOIN
> > (Select
On Feb 15, 2008, at 9:56 AM, Balázs Klein wrote:
given that answers for a questionnaire are stored as a
batch
Not in our setup - for all sorts of reasons (preserving responses
on a connection failure or restart, monitoring response latency in
real time, creating adaptive/branching questio
> Balázs Klein wrote:
> >
> > I was hoping that now with PG supporting plan invalidation it would
> > be possible to return a recordset.
>
> Plan invalidation has nothing to do with it. In Postgres a returned
> recordset can be used as a row source in the FROM clause -- this
> requires data type
On Feb 14, 2008, at 8:19 PM, Joe Conway wrote:
Erik Jones wrote:
See how postgres handles filling the NULLs for you? What you'd
really want to do with this would be to define some functions for
setting and getting a person's answers to a given question or set
of questions so that you cou
> given that answers for a questionnaire are stored as a
> batch
Not in our setup - for all sorts of reasons (preserving responses on a
connection failure or restart, monitoring response latency in real time,
creating adaptive/branching questionnaires) we send each response separately.
> peopl
On Feb 15, 2008, at 6:29 AM, Balázs Klein wrote:
Erik Jones wrote:
First, please stop top-posting. It makes it difficult for both me
and others to know to whom/what you are replying.
Sorry, I don't know much about mailing list customs - I had to look
up what top-posting is. I will behave
Balázs Klein wrote:
I was hoping that now with PG supporting plan invalidation it would
be possible to return a recordset.
Plan invalidation has nothing to do with it. In Postgres a returned
recordset can be used as a row source in the FROM clause -- this
requires data type information to be
Erik Jones wrote:
> First, please stop top-posting. It makes it difficult for both me
> and others to know to whom/what you are replying.
Sorry, I don't know much about mailing list customs - I had to look up what
top-posting is. I will behave now ...
I would prefer to keep the complications f
Joe wrote
> It occurs to me that it shouldn't be terribly difficult to make an
> alternate version of crosstab() that returns an array rather than tuples
> (back when crosstab() was first written, Postgres didn't support NULL
> array elements). Is this worth considering for 8.4?
I think there shou
Erik Jones wrote:
See how postgres handles filling the NULLs for you? What you'd really
want to do with this would be to define some functions for setting and
getting a person's answers to a given question or set of questions so
that you could implement some kind of data integrity with regards
On Feb 14, 2008, at 3:49 PM, Klein Balazs wrote:
My point was to get rid of the the EAV setup. Something like:
CREATE TABLE questions (
question_id serial primary key,
question text not null
);
CREATE TABLE people (
person_id serial primary key,
);
CREATE TABLE answers (
person_id inte
Erik Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 14, 2008 6:14 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] dynamic crosstab
On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:
> -Original Message-
> From: Erik Jone
On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:
-Original Message-
From: Erik Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 14, 2008 5:15 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; 'SunWuKung'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] dynam
SunWuKung'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] dynamic crosstab
On Feb 14, 2008, at 2:04 AM, Balázs Klein wrote:
> Hi,
> ye, hundreds of columns - but there is no helping it, that’s the
> way many questionnaire are and the representation of the responses
> (when not in a
On Feb 14, 2008, at 2:04 AM, Balázs Klein wrote:
Hi,
ye, hundreds of columns - but there is no helping it, that’s the
way many questionnaire are and the representation of the responses
(when not in a database) is always one person per row. I would need
this for exporting, but also to show
Balázs Klein wrote:
>
> Hi,
> Yes I know that SPSS can do this - in fact that is the only way I
> could solve this so far, but that is a very expensive workaround for
> anybody not currently owning SPSS.
Huh, perhaps you could try with PSPP ... (I don't know if it can do it,
but I know it is supp
: Re: [GENERAL] dynamic crosstab
On Wed, 2008-02-13 at 14:04 +0100, Tino Wildenhain wrote:
> Well after all you want a CSV not a table. You could shortcut this
> with a generic query which creates array out of your "columns"
> and join them to a CSV line. This would just be
: SunWuKung
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dynamic crosstab
Hi,
SunWuKung wrote:
> Hi,
>
> I found this to create dynamic crosstabs (where the resulting columns
...
> This could work although for hundreds of columns it looks a bit scary
> for me.
Well I&#x
Hi,
Yes I know that SPSS can do this - in fact that is the only way I could solve
this so far, but that is a very expensive workaround for anybody not currently
owning SPSS.
Thanks.
SWK
-Original Message-
From: jr [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 13, 2008 1:31 PM
: [GENERAL] dynamic crosstab
>
> I found this to create dynamic crosstabs (where the resulting
> columns are not known beforehand):
> http://www.ledscripts.com/tech/article/view/5.html
> (Thanks for Denis Bitouzé on
> http://www.postgresonline.com/journal/index.php?/archives/14-C
&g
On Wed, 2008-02-13 at 14:04 +0100, Tino Wildenhain wrote:
> Well after all you want a CSV not a table. You could shortcut this
> with a generic query which creates array out of your "columns"
> and join them to a CSV line. This would just be outputted as
> one single column from database.
Dependin
Hi,
SunWuKung wrote:
Hi,
I found this to create dynamic crosstabs (where the resulting columns
...
This could work although for hundreds of columns it looks a bit scary
for me.
Well I'd say hundreds of columns are always scary, no matter how you do
it :-)
...
I know that most db people do
>
> I found this to create dynamic crosstabs (where the resulting
> columns are not known beforehand):
> http://www.ledscripts.com/tech/article/view/5.html
> (Thanks for Denis Bitouzé on
> http://www.postgresonline.com/journal/index.php?/archives/14-C
> rossTab-Queries-in-PostgreSQL-using-table
Hi,
I found this to create dynamic crosstabs (where the resulting columns
are not known beforehand): http://www.ledscripts.com/tech/article/view/5.html
(Thanks for Denis Bitouzé on
http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.h
49 matches
Mail list logo
