Create and query in the same page

[ipftalabua]

I was trying to create a table and query it from within the same SQL page.

Unfortunately, I was greeted with an error message: Failed to prepare SQL statement: 'SELECT * FROM zzmytable'

Is there a workaround or an easy fix that I can apply?

The minimal working example triggering the above error message is as follows.

select 'shell' as component,
    'Create and query' as title,
    '/dbg-create-query.sql' as link;

select 'list' as component, 'Output' as title;

create table zzmytable as with t
        (row)
    as (values 
    ('A')
    ) select * from t;
select * from zzmytable;
drop table zzmytable;

Initially, my aim was to create a temporary table and query that. However, not even permanent tables can be queried like that, as the above example shows. In the context of the psql client the above code is just fine. I would be grateful for any hints.

Thanks a lot!

[lovasoa]

Hello and welcome to SQLPage !

SQLPage prepares all your queries in advance, and when the same page is loaded twice, it reruns all the prepared statements. This design allows very good performance, but requires that all the queries can be prepared before they are executed. So if a statement creates a table, it won't be executed before the next one is prepared, and if the second one refers to the table that was created in the first one, it will fail.

This restriction may be lifted in future versions of SQLPage, but in the meantime, you have several options:

• put your CREATE TEMP TABLE statement in sqlpage/on_connect.sql
  • write a postgres procedure that does the job (creating the temp table, using it, then deleting it), and then just run your procedure from the sqlpage sql file.

[lovasoa]

This will be fixed in the next release (already available on docker hub under lovasoa/sqlpage:main)

I changed the statement preparation logic. Each statement is now prepared right before it is executed for the first time, and before later statements in the same file are prepared. This way, we can make sure that a table created at the top of one file is available for use in sql queries that come after, in the same file.

[ipftalabua]

Thanks a lot, this is great news!