Re: express composite type literal as text

On Sun, Feb 22, 2015 at 11:42 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

>
> test=> select * from on_hand where item = '("fuzzy
> dice",42,1.99)'::inventory_item;
>
> item | count
> ------------------------+-------
> ("fuzzy dice",42,1.99) | 1000
> (1 row)
>

So, you have to do the explicit cast? It looks like it. That's not ideal,
we have relied on the assumption that all values (except for NULL) can be
represented as text strings, and PostgreSQL will auto-cast the text to the
appropriate type. Is this case just an exception to a rule that is
generally true and aimed for, or is that just not a good assumption?

Thanks,
Eric

On Sun, Feb 22, 2015 at 11:42 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>