PostgreSQL: syntax error at or near "AS" for tutorial StructureTest

[RAbraham]

Hi, The following code errors out for psql.

# Setup
# Install Logica.
!pip install logica

# Install postgresql server.
!sudo apt-get -y -qq update
!sudo apt-get -y -qq install postgresql
!sudo service postgresql start

# Prepare database for Logica.
!sudo -u postgres psql -c "CREATE USER logica WITH SUPERUSER"
!sudo -u postgres psql -c "ALTER USER logica PASSWORD 'logica';"
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE logica;'

# Connect to the database.
from logica import colab_logica
from sqlalchemy import create_engine
import pandas
engine = create_engine('postgresql+psycopg2://logica:[email protected]', pool_recycle=3600);
connection = engine.connect();
colab_logica.SetDbConnection(connection)

%%logica StructureTest

@Engine("psql");


StructureTest(a: {x: 1, y: 2, z: { w: "hello", v: "world"}});
StructureTest(a: {x: 3, y: 4, z: { w: "bonjour", v: "monde"}});

The Error:

The following query is stored at StructureTest_sql variable.
-- Initializing PostgreSQL environment.
set client_min_messages to warning;
create schema if not exists logica_test;

SELECT * FROM (
  
    SELECT
      STRUCT(1 AS x, 2 AS y, STRUCT('hello' AS w, 'world' AS v) AS z) AS a
   UNION ALL
  
    SELECT
      STRUCT(3 AS x, 4 AS y, STRUCT('bonjour' AS w, 'monde' AS v) AS z) AS a
  
) AS UNUSED_TABLE_NAME  ;

StructureTest

Running predicate: StructureTest

---------------------------------------------------------------------------

ProgrammingError                          Traceback (most recent call last)

/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1760                     self.dialect.do_execute_no_params(
-> 1761                         cursor, statement, context
   1762                     )

20 frames

ProgrammingError: syntax error at or near "AS"
LINE 4:       STRUCT(1 AS x, 2 AS y, STRUCT('hello' AS w, 'world' AS...
                       ^


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)

/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py in do_execute_no_params(self, cursor, statement, context)
    718 
    719     def do_execute_no_params(self, cursor, statement, context=None):
--> 720         cursor.execute(statement)
    721 
    722     def is_disconnect(self, e, connection, cursor):

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "AS"
LINE 4:       STRUCT(1 AS x, 2 AS y, STRUCT('hello' AS w, 'world' AS...
                       ^

[SQL: SELECT * FROM (
  
    SELECT
      STRUCT(1 AS x, 2 AS y, STRUCT('hello' AS w, 'world' AS v) AS z) AS a
   UNION ALL
  
    SELECT
      STRUCT(3 AS x, 4 AS y, STRUCT('bonjour' AS w, 'monde' AS v) AS z) AS a
  
) AS UNUSED_TABLE_NAME  ]
(Background on this error at: http://sqlalche.me/e/14/f405)

[EvgSkv]

Yes, thanks for pointing this out! Unfortunately records aren't supported with psql engine yet. We need a better error message for this.

[RAbraham]

Thanks!