Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT from mytbl;

SELECT from mytbl;

From
brsaweda@gmail.com
Date:
Hi!

Title says it pretty much. I am wondering if there is a short way to
form a query that retrieves all fields of a table (of which I do not
know names and number beforehand) except for one (or more, of which I
know the name(s)). I have stumbled across the need for this a couple
of time during the last few years. Maybe there is a way and I am just
being blind ..

For instance, I would use this in a plpgsql function to select all
fields but "bad_field" from a number of tables. I know the name of the
field I DO NOT want, but number and names of the other fields (which I
want) are not known at the time of writing the function.

Thanx for your hints and please cc answers to my email.


Regards
Erwin

Re: SELECT from mytbl;

From
Rodrigo De León
Date:
On May 29, 5:42 pm, brsaw...@gmail.com wrote:
> Hi!
>
> Title says it pretty much. I am wondering if there is a short way to
> form a query that retrieves all fields of a table (of which I do not
> know names and number beforehand) except for one (or more, of which I
> know the name(s)). I have stumbled across the need for this a couple
> of time during the last few years. Maybe there is a way and I am just
> being blind ..
>
> For instance, I would use this in a plpgsql function to select all
> fields but "bad_field" from a number of tables. I know the name of the
> field I DO NOT want, but number and names of the other fields (which I
> want) are not known at the time of writing the function.
>
> Thanx for your hints and please cc answers to my email.
>
> Regards
> Erwin

create table my_t (
    good_field1 int
    , good_field2 int
    , bad_field int
);

select column_name
from information_schema.columns
where table_name = 'my_t';

 column_name
-------------
 good_field1
 good_field2
 bad_field

select column_name
from information_schema.columns
where table_name = 'my_t'
and column_name <> 'bad_field';

 column_name
-------------
 good_field1
 good_field2

-------------------------------

Now see:

http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Re: SELECT from mytbl;

From
Alvaro Herrera
Date:
Rodrigo De León escribió:

> Now see:
>
> http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Just a quick reminder that it's usually painful to build "generic"
functions in plpgsql because it's not prepared to deal with column or
table names built at run time.  Trying PL/perl or others may prove
simpler.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
On May 30, 2:11 am, Rodrigo De León <rdele...@gmail.com> wrote:
(... useful code example snipped)

> Now see:
>
> http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures....

Thanks for your hints, Rodrigo!
I am aware I can consult pg_catalog / information_schema to retrieve
the information, but that's not quite the "short way" I was hoping
for. Your example is still helpful, though. :)


Regards
Erwin

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
To conclude (to the best of my current knowledge), here is a plpgsql
code sample based on what was said here:

EXECUTE
   'SELECT '
   || (SELECT array_to_string( ARRAY(
SELECT column_name::text
  FROM information_schema.columns
 WHERE table_schema = 'my_schema'
   AND table_name = 'my_relation'
   AND column_name <> 'bad_field'), ', '))
   || ' FROM my_schema.my_relation';

Comments:
- Don't forget to additionally specify the schema - a relation of the
same name may be present in multiple schemas!
- The function array_to_string is the easiest way to form the string
we need.

Actually this is awkwardly slow, as information_schema.columns queries
a ton of other information from various system relations (just have a
look at "explain analyze <query>"!).
So, if you have the necessary privileges to access pg_catalog and
speed is important, this generates the code multiple times faster:

EXECUTE
'SELECT '
|| (SELECT array_to_string(ARRAY(
SELECT a.attname
 FROM pg_class c, pg_namespace nc, pg_attribute a
 WHERE c.relname = 'v_event'
   AND c.relnamespace = nc.oid
   AND nc.nspname = 'stdat'
   AND a.attrelid = c.oid
   AND a.attname <> 'log_up'), ', '))
   || ' FROM stdat.v_event';


Regards
Erwin

Re: SELECT from mytbl;

From
Rodrigo De León
Date:
On May 29, 11:35 pm, Erwin Brandstetter <brsaw...@gmail.com> wrote:
> EXECUTE
> 'SELECT '
> || (SELECT array_to_string(ARRAY(
> SELECT a.attname
>  FROM pg_class c, pg_namespace nc, pg_attribute a
>  WHERE c.relname = 'v_event'
>    AND c.relnamespace = nc.oid
>    AND nc.nspname = 'stdat'
>    AND a.attrelid = c.oid
>    AND a.attname <> 'log_up'), ', '))
>    || ' FROM stdat.v_event';

You might want to add:

     AND a.attnum >=1

to remove "tableoid" and friends from the output.

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
On May 30, 6:48 am, Rodrigo De León <rdele...@gmail.com> wrote:
> On May 29, 11:35 pm, Erwin Brandstetter <brsaw...@gmail.com> wrote:
>
> > EXECUTE
> > 'SELECT '
> > || (SELECT array_to_string(ARRAY(
> > SELECT a.attname
> >  FROM pg_class c, pg_namespace nc, pg_attribute a
> >  WHERE c.relname = 'v_event'
> >    AND c.relnamespace = nc.oid
> >    AND nc.nspname = 'stdat'
> >    AND a.attrelid = c.oid
> >    AND a.attname <> 'log_up'), ', '))
> >    || ' FROM stdat.v_event';
>
> You might want to add:
>
>      AND a.attnum >=1
>
> to remove "tableoid" and friends from the output.

Hmmm .. I don't get "tableoid" and friends in my results as it is
(without "AND a.attnum >=1")
Maybe because all my tables are created "WITHOUT OIDS"?
I have to test this another time ..

Regards
Erwin

Re: SELECT from mytbl;

From
PFC
Date:
On Wed, 30 May 2007 05:24:57 +0200, Erwin Brandstetter
<brsaweda@gmail.com> wrote:

> On May 30, 2:11 am, Rodrigo De León <rdele...@gmail.com> wrote:
> (... useful code example snipped)
>
>> Now see:
>>
>> http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures....
>
> Thanks for your hints, Rodrigo!
> I am aware I can consult pg_catalog / information_schema to retrieve
> the information, but that's not quite the "short way" I was hoping
> for. Your example is still helpful, though. :)
>

    Python example :

import psycopg2
db = psycopg2.connect( host='/var/run/postgresql', user='annonces',
database='annonces' )
cursor = db.cursor()

bad = set(('email','website','comment'))

cursor.execute( "SELECT * FROM contacts LIMIT 0" )

fields = [d[0] for d in cursor.description]
print fields
> ['id', 'name', 'person', 'telephone', 'address', 'zipcode', 'city',
> 'fax', 'email', 'website', 'comment', 'group_id', 'name_search',
> 'address_search']

print "SELECT "+(','.join([d for d in fields if d not in bad]))+" FROM
contacts"
> SELECT
> id,name,person,telephone,address,zipcode,city,fax,group_id,name_search,address_search
> FROM contacts

print "SELECT " + (','.join(set(fields).difference(bad)))+ " FROM contacts"
> SELECT
> city,fax,name_search,name,zipcode,telephone,person,address_search,address,group_id,id
> FROM contacts








>
> Regards
> Erwin
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
On May 30, 6:48 am, Rodrigo De León <rdele...@gmail.com> wrote:

> You might want to add:
>
>      AND a.attnum >=1
>
> to remove "tableoid" and friends from the output.

Now I know why I did not get tableoid & friends: because I am querying
a view which does not yield these fields. But to be on the save side,
the clause should be added. So the complete plpgsql code example looks
like this now (in cleaner format):

EXECUTE
 'SELECT '
 || SELECT array_to_string(ARRAY(
   SELECT a.attname
     FROM pg_class c, pg_namespace nc, pg_attribute a
    WHERE nc.oid = c.relnamespace
      AND a.attrelid = c.oid
      AND a.attnum >= 1
      AND nc.nspname = 'myschema'
      AND c.relname = 'mytbl'
      AND a.attname <> 'bad_field'
    ORDER by a.attnum), ', ')
  || ' FROM myschema.mytbl';

Regards
Erwin

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
On May 30, 7:42 am, l...@peufeu.com (PFC) wrote:
>         Python example :

I found a decent solution for the existing plpgsql function (as
posted). Thanks a lot for the insight into the Python way, though!


Regards
Erwin

Re: SELECT from mytbl;

From
Erwin Brandstetter
Date:
Or even, slightly shorter:

EXECUTE
 'SELECT '
 || array_to_string(ARRAY(
   SELECT a.attname
     FROM pg_class c, pg_namespace n, pg_attribute a
    WHERE n.oid = c.relnamespace
      AND a.attrelid = c.oid
      AND a.attnum >= 1
      AND n.nspname = 'myschema'
      AND c.relname = 'mytbl'
      AND a.attname <> 'bad_field'
    ORDER by a.attnum), ', ')
  || ' FROM myschema.mytbl';

/E