postgres composition type support

[JorisM]

we use the composite types feature of postgres https://www.postgresql.org/docs/10/static/rowtypes.html

If I query a table such as:

data Version
    = Version 
        { major :: Int
        , minor :: Int
        }
data FooBar
    = FooBar
       { foo         ::  Text
       , bar         ::  Version
       } 

the generated SQL looks like.

SELECT "t0"."foo" AS "res0", "t0"."version__major" AS "res1", "t0"."version__minor" AS "res2" FROM "FooBar" AS "t0"

Is there a way to get the query to output something along the lines of:

SELECT "t0"."foo" AS "res0", "(t0.version).major" AS "res1", "(t0.version).minor" AS "res2" FROM "FooBar" AS "t0"

? I saw this comment https://github.com/tathougies/beam/blob/01bbb89c03b93daa2500877a511159df9d91002a/beam-core/test/Database/Beam/Test/SQL.hs#L785 which makes me think this is possible and I missed it in the docs.

[gspia]

I'm having trouble with composite types as well. Have you been able to solve/bypass this problem somehow?

Some notes (see v10 docs):

The following work in posgresql:

    SELECT ("t0")."bar"."major" FROM FooBar AS "t0";
    SELECT ("t0"."bar")."major" FROM FooBar AS "t0";

Double underscores can be removed by using the modifyTable identity $ tableModification to rename bar field. But the generated sql looked like (after some trials)

    SELECT "t0"."minor" as "res1"  and similarly for the 2nd   FROM FooBar AS "t0";
    SELECT "t0"."bar.minor" as "res1"  and similarly for the 2nd   FROM FooBar AS "t0";

and thus it didn't work in posgresql, because it didn't include paranthesis around "t0".

It seems that postgresql could use

    SELECT ("t0")."foo"  FROM FooBar AS "t0";

but would this bring other problems elsewhere (if just adding parenthesis around table name alias in select)?

---

Is this the relevant code that makes the above selects?

[tathougies]

Hey everyone,

There is currently no way to do this, but I'm happy to accept PRs. You'd need to add a function that could take a composite field name and turn it into an appropriate PgExpressionSyntax or PgFieldNameSyntax. Happy to help.

Travis

On Wed, Oct 3, 2018 at 5:36 AM gspia [email protected] wrote:

I'm having trouble with composite types as well. Have you been able to solve/bypass this problem somehow?

Some notes (see v10 docs https://www.postgresql.org/docs/10/static/rowtypes.html):

The following work in posgresql:

SELECT ("t0")."bar"."major" FROM FooBar AS "t0";
SELECT ("t0"."bar")."major" FROM FooBar AS "t0";

Double underscores can be removed by using the modifyTable identity $ tableModification to rename bar field. But the generated sql looked like

SELECT "t0"."renamed1" as "res1"  and similarly for the 2nd   FROM FooBar AS "t0";

and thus it didn't work in posgresql, because it didn't include paranthesis around "t0".

It seems that postgresql could use

SELECT ("t0")."foo"  FROM FooBar AS "t0";

but would this bring other problems elsewhere (if just adding parenthesis around table name alias in select)?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/tathougies/beam/issues/309#issuecomment-426621493, or mute the thread https://github.com/notifications/unsubscribe-auth/AATn8rAA6Gvv8VN98-v5scJBEs7EuKElks5uhK9bgaJpZM4WhLGw .

[gspia]

I'd love to try to help, but be warned that I might need help a lot :)

Thus, first a fork and then a new branch, say, "composites", and then in the beam-postgres something.

Some thoughts / questions:

• composite types can be used in selects, inserts, updates, deletes, functions etc.
• but maybe start with select syntax
• update-syntax (e.g. line 417 in Syntax.hs) uses pgQuotedIndentifier
• Where is the similar function applied when building select syntax? Is it lines 893-897 in Syntax.hs (PgFieldNameSyntax instance)?

This is probably not what you asked but I'm speculating for just to get idea how things work:

• What if we add parenthesis in lines 893-897 in to the qualifiedField -function around the term that corresponds to table name?
• And similarly in lines 916-920 for insert statements: wrap the quoted table name.
• And similarly in lines 417 for update statements: wrap the quoted table name.

The idea would be just to turn table name "t0" into ("t0").

More wild quesses. Should it be rather something like the following?

pgCompositeTblName :: PgExpressionSyntax -> PgSelectCompositeSyntax
pgCompositeTblName expr =
   PgSelectCompositeSyntax $
       emit "(" <> 
       fromPgExpression expr
       emit ")"

(or, instead of fromPgExression, just use pgQuotedIdentifier). Ok, the above things are just wild quesses without understanding the overall structure.

So, could you please lend a helping hand here?