express composite type literal as text

From: Eric Hanson <elhanson(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: express composite type literal as text
Date: 2015-02-22 18:07:52
Message-ID: CACnWs=Xu-GtcksqVbt4gTMdMM8_4Wfhwno_70JNOPvXSbh9=2g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

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)$$;

Thanks,
Eric

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Adrian Klaver 2015-02-22 19:42:32 Re: express composite type literal as text
Previous Message Tom Lane 2015-02-21 22:16:31 Re: gettimeofday cause crash on Windows

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Verite 2015-02-22 19:27:15 Re: Postgres architecture for multiple instances
Previous Message Shanker Singh 2015-02-22 17:21:55 Re: parallel dump fails to dump large tables