From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Eric Hanson <eric(at)aquameta(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: express composite type literal as text |
Date: | 2015-02-27 08:59:04 |
Message-ID: | 54F031D8.1060809@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2/21/15 7:35 PM, Eric Hanson wrote:
> Hi,
>
> I'm trying to use a composite type in a WHERE clause, as described here:
>
> http://www.postgresql.org/docs/9.4/static/rowtypes.html
>
> Just pasting in the examples I get:
>
> 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)$$;
>
> How do I express a composite type literal as text?
>
> Thanks,
> Eric
select * from on_hand where item='("fuzzy dice",42,1.99)'::inventory_item;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(1 row)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Shchukin | 2015-02-27 11:11:14 | Re: Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary |
Previous Message | Jim Nasby | 2015-02-27 08:55:48 | Re: Regarding "Point-in-time Recovery" feature |