Re: column_info()`s COLUMN_DEF values - literal vs function

On Wed, May 27, 2009 at 03:10:53PM -0000, Greg Sabino Mullane wrote:
> 
> > How does your DBI driver represent a default column value in 
> > the results returned by the column_info() method?            
> 
> DBD::Pg quotes enough to allow it to work when passed 
> back in verbatim, with parens to indicate functions:  
> 
> $dbh->do("DROP TABLE abc CASCADE");
> $dbh->do("CREATE TABLE abc(        
>   bar1 TEXT DEFAULT 'current_user',
>   bar2 TEXT DEFAULT current_user   
> )");                               
> $sth = $dbh->column_info(undef, undef, 'abc', '');
> $sth->execute();
> for (@{$sth->fetchall_arrayref({})}) {
>     print "$_->{COLUMN_NAME} - $_->{COLUMN_DEF}\n";
> }
> 
> Outputs:
> 
> bar1 - 'current_user'::text
> bar2 - "current_user"()

So Pg "does the right thing" and goes further by disambiguating it, not
just returning the original text.  Interesting.

[idle aside: I wonder if SQL::Translator knows what to do with that.]

> > If NULL was specified as the default value, then this column is the word
> > NULL, not enclosed in quotation marks.
> 
> However, we are definitely not doing this, and returning undef instead.
> If the purpose of this field according to the ODBC spec is to allow
> new column definitions, then I suppose DBD::Pg should change (although
> undefined does map to NULL in other contexts, and NULL is the default
> DEFAULT, so to speak, for most (all?) database systems).

Doesn't seem like a big issue.

Tim.