Re: plperl no longer provides string representations of composite values

On Fri, Oct 28, 2011 at 16:42, Mischa POSLAWSKY <postgres(at)shiar(dot)org> wrote:
> Composite types are converted to and from Perl hashes since commit
> REL9_1_ALPHA4~146 (Convert Postgres arrays to Perl arrays on PL/perl input
> arguments; 2011-02-17), but are not stringified as claimed by the commit
> message and release notes (unless nested in an array).
>
> To illustrate:
>
> CREATE TYPE foo AS (bar INTEGER, baz TEXT);
> DO $$ my $val = spi_exec_query(q< SELECT        ROW(42,'test')::foo   AS col >)->{rows}->[0]->{col}; elog(NOTICE, "$val ".ref($val)) $$ LANGUAGE plperl;
> NOTICE:  HASH(0xb864a744) HASH

I take it this is your complaint.

> DO $$ my $val = spi_exec_query(q< SELECT ARRAY[ ROW(42,'test')::foo ] AS col >)->{rows}->[0]->{col}; elog(NOTICE, "$val ".ref($val)) $$ LANGUAGE plperl;
> NOTICE: {"(42,test)"} PostgreSQL::InServer::ARRAY

And you are saying this is OK.

You'll note the release notes and commit message only mention that
arrays have the backwards compatible mode. We didn't make a backwards
compatible string version for composites as you could already pass
those in prior to 9.1 (so really nothing changed). What did change is
we unified most of the code so the fact that spi could return arrays
also meant it could return hashes.

So yes that seems like a bit of an oversight. We could fix it by doing
something similar to what we did for arrays. That is turn composites
from hashrefs to blessed objects and overload their string operator.
I'm a bit worried that cure would be worse than whats "broken". Any
code that was using ref to test for 'HASH' would now break. Arrays did
not really have this concern because they were always a string prior
to 9.1. Composites on the other hand have been around a lot longer--
and while I know I don't have any checks like that in /my/ plperl
code, I don't think its too far fetched to imagine someone else does.
I suppose this is a long winded way of saying i'm not very inclined to
dump time into "fixing" this unless there is some kind of consensus
that we should.

Anyway I see 2 work arounds:
- You could change your query to return text:
SELECT ROW(42,'test')::foo::text

- use the new encode_typed_literal() function 9.1 provides

> To make matters worse, received values cannot be used in queries because
> spi_exec_prepared() simply ignores hash arguments:
>
> # DO $$ my $q = spi_prepare('SELECT $1 AS col', 'foo'  ); elog(NOTICE, spi_exec_prepared($q,  { bar=>42, baz=>'test' } )->{rows}->[0]->{col}) $$ LANGUAGE plperl;
> ERROR:  spi_exec_prepared: expected 1 argument(s), 0 passed at line 1.

Not really, the error here is you can't call spi_exec_prepared() like that try:
spi_exec_prepared($q, {}, {bar=>42,...});

Per the documentation the prototype is:
spi_exec_prepared(plan [, attributes], arguments)

We can't distinguish attributes (which are a hashref) from a composite
type (also a hashref), so you need to pass an explicit value for
attributes. That's why its complaining about it expected 1 argument
but 0 were passed. If you pass some other type of reference (like an
array) we know it can't be attributes so we use that as the first
argument.

It may not be pretty, but we didn't want to break the api :-(.