Ask Your Question
0

Can fields of Postgres composite types be referenced in Base queries?

asked 2019-04-17 17:40:03 +0100

rdn gravatar image

When creating a query in Base on a connected Postgres database, Base displays a "SQL syntax error" message when referencing a field of a composite data type. This occurs whether using Postgres's syntax [(composite_type_column).field] or variants that include the table name and omit the parentheses.

Windows 10. LO 6.1.5.2. Postgres 9.4.21.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-04-17 21:32:48 +0100

Ratslinger gravatar image

Hello,

Have not dealt with this type before but find no problem in a Query as you state. Must turn on Run SQL command directly as is the case typically for external DB's running anything other than basic selects.

Set up was done based upon -> 8.16. Composite Types.

Here is record as viewed in pgAdmin3 (pk added):

image description

Here is simple select from Base:

image description

Composite select:

image description

edit flag offensive delete link more

Comments

Thanks. Using Run SQL command directlyalmost does it. The fields are shown, except that one with a domain type equivalent to double precision is shown as zero in all rows.

rdn gravatar imagerdn ( 2019-04-17 22:08:09 +0100 )edit

As stated, this is my first attempt at this type. Don't have anything else established.

Further test, added double precision to table and ran with:

SELECT (on_hand2.item).name, (on_hand2.dpr).r, (on_hand2.dpr).i FROM on_hand2 WHERE (on_hand2.item).price < 9.99;

without problem. retrieved proper data.

Don't know what you have to generate zeros.

Ratslinger gravatar imageRatslinger ( 2019-04-17 22:15:28 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-17 17:40:03 +0100

Seen: 360 times

Last updated: Apr 17 '19