On Sun, Feb 22, 2015 at 11:42 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx> wrote:
From here: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)$$;
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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
