2 simular computed columns / fields for different roles behaving differently

[gaslitbytech]

We have 2 computed columns. items and another item_details the difference is subtle with the item_details function returning a few extra columns only for admin users and the items for our customers.

curl 'http://localhost/rest/products' \
--get \
--data-urlencode 'select=name:name,product_class,items!left(*),...product_types(currency,productTypeName:name)' \
--data-urlencode 'product_class=in.(electric,consumable)' \

{
    "code": "PGRST200",
    "details": "Searched for a foreign key relationship between 'products' and 'items' in the schema 'api', but no matches were found.",
    "hint": "Perhaps you meant 'item_details_rowtype' instead of 'items'.",
    "message": "Could not find a relationship between 'products' and 'items' in the schema cache"
}

When I used item_details!inner(*) it does work as I'd expect.

The computed columns are defined as:

CREATE OR REPLACE FUNCTION api.items(
	api.products)
    RETURNS SETOF api.item_rowtype 
    LANGUAGE sql
AS $BODY$
    select i::api.item_rowtype
    from trading.item_detail i
    where i.product = $1.id
    and (
        (select request.is_admin()) OR 
        (request.has_required_scope(i.required_scope))
    );
$BODY$;

ALTER FUNCTION api.items(api.products)
    OWNER TO migrator;

And

CREATE OR REPLACE FUNCTION api.item_details(
	api.products)
    RETURNS SETOF api.item_details_rowtype 
    LANGUAGE sql
AS $BODY$
    select i::api.item_details_rowtype
    from trading.item_detail i
    where i.product = $1.id
    and (
        (select request.is_admin()) OR 
        (request.has_required_scope(i.required_scope))
    );
$BODY$;

ALTER FUNCTION api.item_details(api.products)
    OWNER TO migrator;

Whas complicates this a little I didn't realise the frontend team were using a library for some time that has made computed columns / fields as in the curl sample above. Though they have been using -> which I believe is a json syntax I'm not aware of.

id:items->product,unitPrice:items:unit_price

That does return the results. Though its my understanding that this -> syntax is behaving like an inner join that isn't desirable as we want a left join.

My question is there a reason the items computed column is not working as a computed column though item_details is?

Also please confirm if the -> would be behaving like a inner join.

We are using PostgREST v12.2.12 and Postgres v17.4

Edit: added question + PostgREST version.

[laurenceisla]

Oh, this is interesting. We have two implementations for functions on table types: Computed fields and Computed relationships. According to your examples you want the latter so the correct syntax for that is: /products?select=items(*), but this only works when the items function returns a table not a composite type for now ( see #3096).

So how is the api.item_rowtype defined, is it a TABLE or a TYPE? To check this, execute:

select relkind from pg_class where relname = 'item_rowtype';

If it's c then it's a composite type, you must use a table to make this work. If it's r then it's a table and it should work, perhaps you need to reload the schema cache.

Whas complicates this a little I didn't realise the frontend team were using a library for some time that has made computed columns / fields as in the curl sample above. Though they have been using -> which I believe is a json syntax I'm not aware of.

Yes, this works when treating the function as a computed field (i.e. ?select=items instead of ?select=items(*)). So you can access the content inside as json.

Also please confirm if the -> would be behaving like a inner join.

It looks like it's working as an inner join 'cause the underlying query does this for the computed field in products?select=*,items :

SELECT "api"."products".*,
       "api"."products"."items"
FROM "api"."products";

Whereas, for a computed relationship, it does an explicit LEFT JOIN by default.

[gaslitbytech]

Yes it is an composite type with c which I assumed just worked until now.

Thank you for the response and linking to the existing issue with relevant discussion. I'll be likely also implementing an table instead of the composite type.

create type api.item_rowtype as
(
    product                        INT,
    price                             NUMERIC,
    quantity                        NUMERIC,
    ...

Edit: formatting only

[gaslitbytech]

Also relevant item_details_rowtype is a VIEW liek below so we could reuse.

create or replace view api.item_details_rowtype as
select (null:: api. item_rowtype).*
     , null::text as admin_notes
where false;

Switching item_rowtype to use an empty view as a work around some in the issue discussion mentioned will be a good solution for us too. Thanks again.