Thread: Selecting all but a few fields in a query

I have a table with a lot of fields, some of which are type TEXT. I
seem to have a lot of occasions where I'd like to see almost all of
the fields in a query but a SELECT * will pull in the text fields,
which then wrap across the screen, even using \x. Is there a variation
of SELECT that is something like 'select all fields except these here
WHERE...'? This would save me a lot of typing. I checked the docs but
missed it if it's in there.


thx

r

On 11/5/07, thefronny@gmail.com <thefronny@gmail.com> wrote:
> I have a table with a lot of fields, some of which are type TEXT. I
> seem to have a lot of occasions where I'd like to see almost all of
> the fields in a query but a SELECT * will pull in the text fields,
> which then wrap across the screen, even using \x. Is there a variation
> of SELECT that is something like 'select all fields except these here
> WHERE...'? This would save me a lot of typing. I checked the docs but
> missed it if it's in there.

Nope, no such animal exists.

You could write something like it in pl/tcl or pl/C or such.

thefronny@gmail.com wrote:
> I have a table with a lot of fields, some of which are type TEXT. I
> seem to have a lot of occasions where I'd like to see almost all of
> the fields in a query but a SELECT * will pull in the text fields,
> which then wrap across the screen, even using \x. Is there a variation
> of SELECT that is something like 'select all fields except these here
> WHERE...'? This would save me a lot of typing. I checked the docs but
> missed it if it's in there.
>

You could create a new type with the columns you'd like to see:

CREATE TYPE my_type AS
(
   this    TIMESTAMP NOT NULL
   that    INT4 NOT NULL,
   other    VARCHAR(128),
);

CREATE FUNCTION my_function()
   RETURNS SETOF my_type IMMUTABLE
AS $_$
   DECLARE
     the_type my_type%rowtype;
   BEGIN
     FOR the_type IN
       EXECUTE 'SELECT t.this, t.that, t.other FROM my_table WHERE ...'
       LOOP
         RETURN NEXT the_type;
       END LOOP;
     RETURN;
   END;
$_$ LANGUAGE plpgsql

brian