Re: PostgreSQL Macro Query

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/