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