From: Thomas F.O'Connell <tfo@sitening.com>
To: Erik Wasser <erik.wasser@iquer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Extracting fieldnames from a TABLE
Date: Wed, 1 Sep 2004 11:28:12 -0500
Message-ID: <EAE3389F-FC33-11D8-A557-000D93AE0944@sitening.com> (raw)
In-Reply-To: <200409011714.49671.erik.wasser@iquer.net>
References: <200409011714.49671.erik.wasser@iquer.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com, erik.wasser@iquer.net
Subject: Re: Extracting fieldnames from a TABLE
In-Reply-To: <EAE3389F-FC33-11D8-A557-000D93AE0944@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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