Re: PostgreSQL Macro Query

Поиск
Список
Период
Сортировка
От Sam Mason
Тема Re: PostgreSQL Macro Query
Дата
Msg-id 20090930104142.GX5407@samason.me.uk
обсуждение исходный текст
Ответ на PostgreSQL Macro Query  (Bronagh Grimes <Bronagh.Grimes@distinct.ie>)
Список pgsql-general
On Wed, Sep 30, 2009 at 11:13:06AM +0100, Bronagh Grimes wrote:
> I have written some code to calculate basic summary stats and wish to
> then incorporate this code within a macro... I want to run the code
> many multiple times and define the variables (on which the summary
> statistics are calculated) outside of the basic code.

I'd normally do this "outside" Postgres, maybe from something like
Python or Perl.

> For example, see some very basic code below... I would use a macro so
> that I don't have to change 'STUDY_GROUP' in the code each time.

If you want to stay inside PG and use psql, you could rewrite the query
to make this less of a problem.

> select "STUDY_GROUP" , count("STUDY_GROUP")
> from "TABLE1" group by "STUDY_GROUP";

  SELECT "STUDY_GROUP", COUNT(*)
  FROM "TABLE1"
  GROUP BY 1;

Will do the same thing; except where the column is NULL.  You will have
been getting zero before, but now you'll be told how many null entries
you have--this may or may not be what you want.  If you don't want it,
you could do:

  SELECT v, COUNT(v)
  FROM (SELECT "STUDY_GROUP" AS v FROM "TABLE1") x
  GROUP BY v;

There would still only be one name to change then and PG would optimize
the query to do the same thing either way so performance shouldn't be
affected.

Hope that helps!

--
  Sam  http://samason.me.uk/

В списке pgsql-general по дате отправления:

Предыдущее
От: Bronagh Grimes
Дата:
Сообщение: PostgreSQL Macro Query
Следующее
От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: Postgresql Web Hosting