Обсуждение: express composite type literal as text
Hi,
I'm trying to use a composite type in a WHERE clause, as described here:
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 passed in as text. 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)';
select * from on_hand where item=$$("fuzzy dice",42,1.99)$$;
How do I express a composite type literal as text? Any workarounds? The text-based representation of values is a hard requirement in my project...
Thanks,
Eric
On Sat, Feb 21, 2015 at 4:58 PM, Eric Hanson <eric@aquameta.com> 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 passed in as > text. 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)'; > select * from on_hand where item=$$("fuzzy dice",42,1.99)$$; > > How do I express a composite type literal as text? Any workarounds? The sure! all you have to to is cast it: select * from on_hand where item='("fuzzy dice",42,1.99)'::on_hand; merlin