Postgres Pro
Downloads
Home   >   mailing lists

Thread: express composite type literal as text

express composite type literal as text

From
Eric Hanson
Date:
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

Re: express composite type literal as text

From
Merlin Moncure
Date:
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