pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Extracting fieldnames from a TABLE
2+ messages / 2 participants
[nested] [flat]

* Extracting fieldnames from a TABLE
@ 2004-09-01 15:14 Erik Wasser <erik.wasser@iquer.net>
  2004-09-01 16:28 ` Re: Extracting fieldnames from a TABLE Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Erik Wasser @ 2004-09-01 15:14 UTC (permalink / raw)
  To: pgsql-sql

Hi community,

I would like to retrieve all the fieldnames of a given table. In the 
perl module Tie::DBI[1] i found the following fragment:

   $dbh->prepare("LISTFIELDS $table");

in the case the DB supports this (Tie::DBI thinks so for Pg) or the 
alternative is:

   $dbh->prepare("SELECT * FROM $table WHERE 0=1");

The first one doesn't work in my PostgreSQL 7.4.3:

% LISTFIELDS foobar;
ERROR:  syntax error at or near "LISTFIELDS" at character 1
%

and the seconds one looks ugly. Is there a solution for the problem?

Greetings

[1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm

-- 
So long... Fuzz



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Extracting fieldnames from a TABLE
  2004-09-01 15:14 Extracting fieldnames from a TABLE Erik Wasser <erik.wasser@iquer.net>
@ 2004-09-01 16:28 ` Thomas F.O'Connell <tfo@sitening.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Thomas F.O'Connell @ 2004-09-01 16:28 UTC (permalink / raw)
  To: Erik Wasser <erik.wasser@iquer.net>; +Cc: pgsql-sql

One way to do this is to use the column_info database handle method.

Here's a little perl script that accepts a table name as an argument 
and returns the column names:

#!/usr/bin/perl

use DBI;
use strict;

my( $database, $table ) = @ARGV;
my $dbh = DBI->connect( "dbi:Pg:dbname=$database", 'postgres' );
my $sth = $dbh->column_info( undef, 'public', $table, '%' );
$sth->execute;

while( my @row = $sth->fetchrow_array ) {
         print join( ' ', $row[ 3 ] ), "\n";
}

$sth->finish;
$dbh->disconnect;


This could be easily modified to stick the contents of $row[ 3 ] into 
an array. You'd have to modify the user and schema as appropriate for 
your database.

The fourth parameter to column_info is a wildcard so you get everything.

-tfo

On Sep 1, 2004, at 10:14 AM, Erik Wasser wrote:

> Hi community,
>
> I would like to retrieve all the fieldnames of a given table. In the
> perl module Tie::DBI[1] i found the following fragment:
>
>    $dbh->prepare("LISTFIELDS $table");
>
> in the case the DB supports this (Tie::DBI thinks so for Pg) or the
> alternative is:
>
>    $dbh->prepare("SELECT * FROM $table WHERE 0=1");
>
> The first one doesn't work in my PostgreSQL 7.4.3:
>
> % LISTFIELDS foobar;
> ERROR:  syntax error at or near "LISTFIELDS" at character 1
> %
>
> and the seconds one looks ugly. Is there a solution for the problem?
>
> Greetings
>
> [1]http://search.cpan.org/~lds/Tie-DBI-0.93/lib/Tie/DBI.pm
>
> -- 
> So long... Fuzz




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-09-01 16:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-01 15:14 Extracting fieldnames from a TABLE Erik Wasser <erik.wasser@iquer.net>
2004-09-01 16:28 ` Thomas F.O'Connell <tfo@sitening.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox