PostgreSQL user defined type member compare

Question

I have the following table definition:

 CREATE TYPE product_limit_type AS (
    product_code varchar(5),
    limit_type varchar(30),
    limit_value numeric(4,0)
);

CREATE TABLE rule_locks (
    session_id bigint,
    rule_id bigint,
    product_limit product_limit_type
);

When I execute the following query:

DELETE FROM rule_locks
WHERE (session_id=session_id_ AND product_limit.product_code=product_code_);

I get this error from the server:

ERROR:  missing FROM-clause entry for table "product_limit"
LINE 1: ...FROM rule_locks WHERE (session_id=session_id_ AND product_li...
                                                         ^

session_id_ and product_code_ are pre-initialized variables of corresponding types.

How is it supposed to erase/modify a row, whose cell member variable satisfies given condition?

Daniel Vérité · Accepted Answer · 2013-01-28 10:34:30Z

3

There should be parentheses around the field's name to access its subfields, as in:

DELETE FROM rule_locks WHERE (session_id=session_id_
     AND (product_limit).product_code=product_code_);

See Accessing Composite Types in the documentation.

answered Jan 28, 2013 at 10:34

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL user defined type member compare

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related