Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL Macro Query

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL Macro Query
Date: 2009-09-30 10:41:42
Message-ID: 20090930104142.GX5407@samason.me.uk (view raw or flat)
Thread:
Lists: 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/

In response to

pgsql-general by date

Next:From: Devrim GÜNDÜZDate: 2009-09-30 11:49:40
Subject: Re: Postgresql Web Hosting
Previous:From: Bronagh GrimesDate: 2009-09-30 10:13:06
Subject: PostgreSQL Macro Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group