From: | Eric Hanson <eric(at)aquameta(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | express composite type literal as text |
Date: | 2015-02-21 22:58:06 |
Message-ID: | CACA6kxj_srX2rOFXmpFMAkV4-ntssy=PRaY=v1BF2K4rWnx9+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
text-based representation of values is a hard requirement in my project...
Thanks,
Eric
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-02-21 23:08:18 | Re: Postgres architecture for multiple instances |
Previous Message | Samuel Smith | 2015-02-21 22:01:47 | Postgres architecture for multiple instances |