express composite type literal as text - Mailing list pgsql-general

From Eric Hanson
Subject express composite type literal as text
Date
Msg-id CACnWs=Xu-GtcksqVbt4gTMdMM8_4Wfhwno_70JNOPvXSbh9=2g@mail.gmail.com
Whole thread Raw
Responses Re: express composite type literal as text
Re: express composite type literal as text
List pgsql-general
Hi,

How do I express a composite type literal as text?

I'm trying to use a composite type in a WHERE clause.  The examples in the docs


say:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);


Now I want to query for that row, specifying the item in the WHERE clause.  I can't use the ROW() notation, because all values need to be represented as text over a REST api.  But I can't seem to get the text-based syntax to work:

select * from on_hand where item='("fuzzy dice",42,1.99)';

yeilds

ERROR:  input of anonymous composite types is not implemented

I've tried various forms of quote escaping and dollar quoting as the docs suggest, but they all produce that same error:

select * from on_hand where item='(\\\"fuzzy dice\\\",42,1.99)';
select * from on_hand where item=$$("fuzzy dice",42,1.99)$$;


Thanks,
Eric

pgsql-general by date:

Previous
From: Shanker Singh
Date:
Subject: Re: parallel dump fails to dump large tables
Next
From: "Daniel Verite"
Date:
Subject: Re: Postgres architecture for multiple instances