Re: Alias "all fields"?

as everyone has pointed out it does not seem like the best table design
and querying for these fields as normal course of business does not seem
that great, but if you wanted to audit tables like these once in a while
you could easily do it using your favorite scripting language or SQL
itself. here's a simple psql example to get you started:

test=> \t
Tuples only is off.
test=> \a
Output format is aligned.
test=> \t
Showing only tuples.
test=> \a
Output format is unaligned.
test=> \o /tmp/null-test.sql
test=> select 'select ''' || upper(table_name) || '''|| '' not null rows
count: '' || count(*) from '||table_name||' where ' ||
array_to_string(array(select column_name::text from
information_schema.columns c where c.table_name = t.table_name),' is not
null and ') || ' is not null;' from information_schema.tables t where
table_schema = 'test' and table_name like 'emp%';
test=> \o
test=> \i /tmp/null-test.sql
EMPLOYEE not null rows count: 89
EMPLOYEE_ROLE not null rows count: 11
EMPLOYEE_ROLE_PRIVILEGE not null rows count: 266
EMPLOYEE_PRIVILEGE not null rows count: 53
EMPLOYEE_PRIVILEGE_GROUP not null rows count: 9

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> Stefan Schwarzer
> Sent: Thursday, September 06, 2007 4:43 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Alias "all fields"?
>
> Hi there,
>
> I guess I am demanding too much.... But it would be cool to
> have some
> kind of alias for "all fields".
>
> What I mean is this here:
>
> Instead of this:
>
> SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
> AND .... y2005 NOT NULL
>
> I would like to have this:
>
> SELECT * FROM gdp WHERE all-fields NOT NULL
>
> This is, because my tables have different - and a different
> number of
> fields.
>
> In principal, I actually just want to have the number of
> fields which
> are NOT NULL...
>
> Thanks for any advice.
>
> Stef
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>