pragma_table_info column name is interpreted as reserved work?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pragma_table_info column name is interpreted as reserved work?

Keith Medcalf

select m.name       as TableName,
       i.cid        as ColumnID,
       i.name       as ColumnName,
       i.type       as Affinity,
       i.notnull    as CanBeNull,
       i.dflt_value as DefaultValue,
       i.pk         as PrimaryKeySeq
  from sqlite_master as m,
       pragma_table_info(m.name) as i
 where m.type='table';

returns Error: near "notnull": syntax error

however, quoting "notnull" works:

select m.name       as TableName,
       i.cid        as ColumnID,
       i.name       as ColumnName,
       i.type       as Affinity,
       i."notnull"  as CanBeNull,
       i.dflt_value as DefaultValue,
       i.pk         as PrimaryKeySeq
  from sqlite_master as m,
       pragma_table_info(m.name) as i
 where m.type='table';

returns:

advisory|0|advisory|integer|0||1
advisory|1|advisoryname|text|1|'Unknown'|0
advlink|0|program|text|1||1
advlink|1|advisory|integer|1||2
crew|0|crew|integer|0||1
crew|1|surname|text|1|''|0
crew|2|givenname|text|1|''|0
crewlink|0|program|text|1||1
crewlink|1|crew|integer|1||3
crewlink|2|role|integer|1||2
...

Is there a way to make the "column names" returned by a pragma (eg, pragma_table_info) table not be interpreted as a reserved word?  While it is not a bug per-se, it is annoying to have to quote (what one would hope are) properly composed column names (even though there are many folks who habitually use column name quoting as a matter-of-course because many ill-conceived tools generate badly-formed column-names or quote wildly).  It one typically only used well-formed column names, then having to quote is downright ugly ...

---
Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
 -- Hunter S. Thompson




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma_table_info column name is interpreted as reserved work?

Domingo Alvarez Duarte
Hello Keith !

You can try this that works:

select m.name       as TableName,
        i.cid        as ColumnID,
        i.name       as ColumnName,
        i.type       as Affinity,
        i."notnull"    as CanBeNull,
        i.dflt_value as DefaultValue,
        i.pk         as PrimaryKeySeq
   from sqlite_master as m,
        pragma_table_info(m.name) as i
  where m.type='table';


On 12/02/17 15:24, Keith Medcalf wrote:

> select m.name       as TableName,
>         i.cid        as ColumnID,
>         i.name       as ColumnName,
>         i.type       as Affinity,
>         i.notnull    as CanBeNull,
>         i.dflt_value as DefaultValue,
>         i.pk         as PrimaryKeySeq
>    from sqlite_master as m,
>         pragma_table_info(m.name) as i
>   where m.type='table';

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma_table_info column name is interpreted as reserved work?

Keith Medcalf

On Sunday, 12 February, 2017 11:03, Domingo Alvarez Duarte <[hidden email]> wrote:

> Hello Keith !
 
> You can try this that works:
 

> select m.name       as TableName,
>         i.cid        as ColumnID,
>         i.name       as ColumnName,
>         i.type       as Affinity,
>         i."notnull"    as CanBeNull,
>         i.dflt_value as DefaultValue,
>         i.pk         as PrimaryKeySeq
>    from sqlite_master as m,
>         pragma_table_info(m.name) as i
>   where m.type='table';

Yes, I am aware that it works if you quote the apparently reserved word "notnull".  That is however ugly beyond belief and quoted identifiers make my eyes bleed (just like Perl :) ).  It would be nice if the default column name were not a reserved word ...
 

> On 12/02/17 15:24, Keith Medcalf wrote:
> > select m.name       as TableName,
> >         i.cid        as ColumnID,
> >         i.name       as ColumnName,
> >         i.type       as Affinity,
> >         i.notnull    as CanBeNull,
> >         i.dflt_value as DefaultValue,
> >         i.pk         as PrimaryKeySeq
> >    from sqlite_master as m,
> >         pragma_table_info(m.name) as i
> >   where m.type='table';
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma_table_info column name is interpreted as reserved work?

James K. Lowden
On Sun, 12 Feb 2017 12:40:27 -0700
"Keith Medcalf" <[hidden email]> wrote:

> I am aware that it works if you quote the apparently reserved word
> "notnull".  

That's the queer part.  I can imagine "not" and "null" as keywords, but
not "nonnull".  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users