(using SQLite 3.39.3)

Recently, I had to write ordinary PRAGMA statements where the equivalent pragma functions would have been more convenient¹.

It seems to me that pragma functions don't observe the effect of CREATE TABLE when in the middle of a transaction.

The documentation says pragma functions are experimental, though I haven't found this limitation explicitly documented.

After some web searches and having not found anything to clarify the matter, I decided to write this message. Is the difference in behavior imposed by the implementation constraints?

Repro:

$ sqlite3 <<EOF
BEGIN;
CREATE TABLE t(x);
PRAGMA table_info(t);
ROLLBACK;
EOF
0|x||0||0

$ sqlite3 <<EOF
BEGIN;
CREATE TABLE t(x);
SELECT pragma_table_info(t);
ROLLBACK;
EOF
Parse error near line 3: no such column: t
  SELECT pragma_table_info(t);
             error here ---^

¹ For the sake of completeness, example Tcl code showing what I was trying to accomplish:

proc create-triggers {tbl} {
  #set collist [db one "SELECT name FROM pragma_table_info(${tbl})"]
  set collist {}
  db eval "PRAGMA table_info(${tbl})" {lappend collist $name}

  # ... code that uses $collist omitted ...

  set sql "CREATE TRIGGER ${tbl}_ut AFTER UPDATE ON $tbl BEGIN\n"
  #db eval "SELECT name FROM pragma_table_info(${tbl}) WHERE pk=1 UNION SELECT 'from' FROM pragma_foreign_key_list(${tbl})" col {
  #  append sql "  SELECT RAISE(ROLLBACK,'${tbl}.$col(name) is immutable') WHERE OLD.$col(name) != NEW.$col(name);\n"
  #}
  set immulist {}
  db eval "PRAGMA table_info(${tbl})" {
    expr {$pk ? [lappend immulist $name] : [continue]}
  }
  db eval "PRAGMA foreign_key_list(${tbl})" {
    lappend immulist $from
  }
  foreach name [lsort -unique $immulist] {
    append sql "  SELECT RAISE(ROLLBACK,'${tbl}.$name is immutable') WHERE OLD.$name != NEW.${name};\n"
  }

  # ...
}

# ...

db transaction {
    # ...
    create-triggers tbl1
    create-triggers tbl2
    # ...
}

SELECT pragma_table_info(t);

You need to give it the table name as a string, and since it's a table-valued function you need to select FROM it, not select it directly.

SELECT * FROM pramga_table_info('t');

Thanks, David.

The missing '* FROM' was an editing mistake on my side when posting.

The quoting on the argument does make a difference, and the error message "no such column ..." misguided my reasoning!

I think I incorrectly relied on SQLite "getting it right" for PRAGMA table_info(t);, even though there t should also be quoted (?).

I fixed the quoting by fixing my TCL code as below, letting eval substitute variables (instead of the TCL interpreter) and automatically quote SQL strings:

-  #set collist [db one "SELECT name FROM pragma_table_info(${tbl})"]
-  set collist {}
-  db eval "PRAGMA table_info(${tbl})" {lappend collist $name}
+  set collist [db eval {SELECT name FROM pragma_table_info($tbl)}]

Thanks again!

Rodolfo

Further to what David said, the following are equivalent:

pragma <schema>.<pragmaname>(<arg>);

and

select * from pragma_<pragmaname> where schema='<schema>' and arg='<arg>'

So, if
<schema> is main
<pragmaname> is table_info
<arg> is t

Then that translates to:

pragma main.table_info(t)

is equivalent to

select * from pragma_table_info where schema='main' and arg='t'

Thanks for sharing, Keith.

It would be useful to have this example of equivalence documented in https://www.sqlite.org/pragma.html#pragfunc, because now that I read the example I think I understand better the prose in that section.

The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function, with the schema as an optional, last argument.

So, if I understand correctly, your example is also equivalent to

SELECT * FROM pragma_table_info('t','main');