logica PostgreSQL support

Most of logica works with PostgreSQL except for the following list:

[x] Output of structures is not supported. E.g. with "psql" engine currently you can't do Q(contact: {name: "John", email: "[email protected]"}); This is because PostgreSQL requires pre-defining types before using the structs and this is not implemented.
[x] ArgMaxK is ignoring the limit, so picking a few maximal values from a very large set is impossible. We don't know how to fix this, as PostgreSQL does not support LIMIT in ARRAY_AGG.

Originally posted by @EvgSkv in https://github.com/EvgSkv/logica/discussions/20#discussioncomment-607318

Apr 14 '21 18:04 v217

Another problem we have is that ArgMax/ArgMin only work for numbers to numbers. But I have an idea now of how to make ArgMax work, which is the most important missing feature at the moment. Should be able to do it soon.

Apr 21 '21 22:04 EvgSkv

I've made some upgrades. Now ArgMax/ArgMin work for any types. PostgreSQL is well supported now. You should be able to do anything. There is still some friction, as you can't put records in tables. To use a record you would have to define a type and use SqlExpr in Logica. Let me know if there are any problems.

Apr 25 '21 01:04 EvgSkv

That's awesome! Up to now everything worked great!

Apr 25 '21 07:04 v217

This is exciting to hear. Thanks for working on this.

Apr 25 '21 11:04 RAbraham

Sorry for not understanding this: In the primes.l example, the output SQL uses a function called GENERATE_ARRAY, which is Standard SQL specific? What would be the Postgres equivalent?

SELECT
  x_4 AS prime
FROM
  UNNEST(GENERATE_ARRAY(0, 30 - 1)) as x_4
WHERE
  (x_4 > 1) AND
  ((SELECT
    MIN(1) AS logica_value
  FROM
    UNNEST(GENERATE_ARRAY(0, 30 - 1)) as x_7
  WHERE
    (x_7 > 1) AND
    (x_7 != x_4) AND
    MOD(x_4, x_7) = 0) IS NULL);

May 03 '21 08:05 Peder2911

Hello @Peder2911 ! In PostgreSQL this is achieved with GENERATE_SERIES. Adding @Engine("psql") in the Logica program makes it compile to SQL that is compatible with Postgres. For example primes_postgres.l is a version of primes.l that runs on Postgres and uses GENERATE_SERIES.

May 03 '21 15:05 EvgSkv

Wow, that's great! I have wanted to get into logic programming for so long, I think Logica seems like a cool "gateway language".

Thank you so much @EvgSkv ;)

May 03 '21 15:05 Peder2911

Support for user types (aka structures, records) and type inference were added to Logica. Now Logica supports PostgreSQL fully.

https://opensource.googleblog.com/2023/09/full-support-of-postgresql-engine-comes-to-logica.html

Oct 02 '23 16:10 EvgSkv