Re: express composite type literal as text

On 02/22/2015 10:07 AM, Eric Hanson wrote:
> 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
>
> http://www.postgresql.org/docs/9.4/static/rowtypes.html
>
> 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)$$;

From here:

http://www.postgresql.org/docs/9.3/static/sql-expressions.html

4.2.13. Row Constructors

So,

test=> INSERT INTO on_hand VALUES (ROW('bobble dog', 42, 5.99), 1000);
INSERT 0 1

test=> select * from on_hand ;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
 ("bobble dog",42,5.99) |  1000
(2 rows)



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

          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)




>
>
> Thanks,
> Eric


--
Adrian Klaver
adrian.klaver@aklaver.com