Thread: express composite type literal as text

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@aklaver.com

Eric Hanson <elhanson@gmail.com> writes:
> How do I express a composite type literal as text?

The rules are given in the manual ...

> 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

That message isn't telling you that you've got a problem with the data
syntax, it's telling you that you need to cast the literal to a named
composite data type.  This works:

# select * from on_hand where item='("fuzzy dice",42,1.99)'::inventory_item;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)

Now, I'm not too sure *why* it's making you do that --- seems like the
default assumption ought to be that the literal is the same type as
the variable it's being compared to.  Perhaps there's a bug in there,
or perhaps there's no easy way to avoid this requirement.  But that's
what the requirement is today.

            regards, tom lane

On Sun, Feb 22, 2015 at 12:18:21PM -0800, Eric Hanson wrote:
> Got it.  Ok, I'm reporting this as a bug.  Is this a bug?  Being able to
> always express literals as text is a really valuable assumption to be able
> to rely on.

If I had to guess (I guess someone more authoritative than I will
chime in), I suspect this is a side effect of the change a few years
ago that removed a very large number of automatic typecasts.
Experience with those automatic typecasts revealed that while they
were handy lots of the time, when they failed they did really bad
things.  So the developers dramatically reduced the number of such
cases.  Some convenience was lost (I still get tripped up from time to
time, but I'm not doing Pg work every day), but the overall
reliability of things was increased.  So I'd say it's probably not a
bug.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Eric Hanson <elhanson@gmail.com> writes:
> On Sun, Feb 22, 2015 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Now, I'm not too sure *why* it's making you do that --- seems like the
>> default assumption ought to be that the literal is the same type as
>> the variable it's being compared to.  Perhaps there's a bug in there,
>> or perhaps there's no easy way to avoid this requirement.  But that's
>> what the requirement is today.

> Got it.  Ok, I'm reporting this as a bug.  Is this a bug?  Being able to
> always express literals as text is a really valuable assumption to be able
> to rely on.

Well, it's an unimplemented feature anyway.  I poked into it and noticed
that the equivalent case for arrays works, because that operator is
"anyarray = anyarray".  enforce_generic_type_consistency() observes that
we have an unknown literal that's going to be passed to an anyarray
function argument, so it resolves "anyarray" as the actual array type
determined from the other anyarray argument position.

There's no corresponding behavior for RECORD, because RECORD is not
treated as a polymorphic type for this purpose -- in particular, there is
no built-in assumption that the two arguments passed to record_eq(record,
record) should be the same record type.  (And, indeed, it looks like
record_eq goes to some effort to cope with them not being identical;
this may be essential to make dropped-column cases work desirably.)

Conceivably we could invent an ANYRECORD polymorphic type, extend the
polymorphic type logic to deal with that, and redefine record_eq as taking
(anyrecord, anyrecord).  However that'd likely break some scenarios along
with fixing this one.  It'd require some research to figure out what's
the least painful fix.  In any case, anything involving a new datatype is
certainly not going to be a back-patchable bug fix.

Given that it's worked like this pretty much forever, and there have been
few complaints, it's probably not going to get to the front of anyone's
to-do list real soon ...

            regards, tom lane