Quantcast

Pragma table-valued functions used in views in an attached database.

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

Pragma table-valued functions used in views in an attached database.

Constantine Yannakopoulos
Hi,

Trying to create an attached information schema using the instructions
in the additional notes of the "Pragma functions" section of
https://www.sqlite.org/pragma.html I have come across an issue. I
execute the following statements in any sqlite database:

attach :memory: as info;

drop view if exists info.table_columns;

create view info.table_columns as
select
  tbl_name as table_name,
  c.cid as column_id,
  c.name as column_name,
  c."type" as "type",
  c."notnull" as "notnull",
  c.dflt_value,
  c.pk
from sqlite_master m, pragma_table_info(m.tbl_name) c
where m.type = 'table';

Then, when I execute:

select * from table_columns;
or
select * from info.table_columns;

I get the error 'no such table: info.pragma_table_info'. But when I
execute the view's query directly I get the expected results. I get a
similar error if I try to use the example provided in the document:

CREATE VIEW info.indexed_columns AS
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_master AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
WHERE m.type='table'
ORDER BY 1;

If I create the view in the main database it works as expected but I
would prefer to be able to create it in an attached memory database so
that it does not persist in the database file.

Am I doing something wrong/not supported?

One more note: I believe it is impossible to construct a result set
that contains all databases (main and attached) and all objects (e.g.
tables) within each database with a single SQL statement that works
without "knowledge" of the attached databases (else it is easy with a
union). If someone knows a way please let me know. If there isn't,
perhaps the addition of two more pragmas:

  pragma table_list(database-name);
  pragma view_list(database-name);

would help.

PS: I know all this is possible by creating eponymous virtual tables
but the difficulty level for this is much higher.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pragma table-valued functions used in views in an attached database.

Simon Slavin-3

On 12 Feb 2017, at 10:39am, Constantine Yannakopoulos <[hidden email]> wrote:

> perhaps the addition of two more pragmas:
>
>  pragma table_list(database-name);
>  pragma view_list(database-name);
>
> would help.

You might have missed

<https://www.sqlite.org/pragma.html#pragma_table_info>

which, despite its name, also works with views.

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

Re: Pragma table-valued functions used in views in an attached database.

Constantine Yannakopoulos
On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin <[hidden email]> wrote:
> You might have missed
>
> <https://www.sqlite.org/pragma.html#pragma_table_info>
>
> which, despite its name, also works with views.

No, this returns the columns of a single table. Suggested pragma
table_list/view_list would return all tables/views in a database.

I know I can get that from sqlite_master, but having to prefix it with
the database name as part of the query syntax:

  select * from attached_db.sqlite_master;

makes it impossible to join with pragma database_list:

  select * from pragma_database_list as dblist, dblist.name.sqlite_master;

cannot work while

  select * from pragma_database_list as dblist,
pragma_table_list(dblist.name) as tbllist;

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

Re: Pragma table-valued functions used in views in an attached database.

Simon Slavin-3

On 12 Feb 2017, at 11:10am, Constantine Yannakopoulos <[hidden email]> wrote:

> On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin <[hidden email]> wrote:
>> You might have missed
>>
>> <https://www.sqlite.org/pragma.html#pragma_table_info>
>>
>> which, despite its name, also works with views.
>
> No, this returns the columns of a single table. Suggested pragma
> table_list/view_list would return all tables/views in a database.
>
> I know I can get that from sqlite_master, but having to prefix it with
> the database name as part of the query syntax:
>
>  select * from attached_db.sqlite_master;
>
> makes it impossible to join with pragma database_list:
>
>  select * from pragma_database_list as dblist, dblist.name.sqlite_master;

Okay, I see what you mean.  I don’t know of a way to do this in one command if you may have attached databases.

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