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